Use absolute reference in Excel to keep a cell constant when you copy or autofill

Posted on:  03/24/2017
Use absolute reference in Excel to keep a cell constant when you copy or autofill

There may be times when you do not want a cell reference to change when filling cells. Unlike relative references, absolute references do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant.

The keyboard shortcut for absolute reference is F4. If you have a laptop and F4 does not work, try holding down FN and pressing F4. An absolute reference is designated in a formula by the addition of a dollar sign ($).

Examples of an absolute reference.

Absolute reference (1)

Absolute reference (2)

YouTube video on creating an Absolute Reference

Three absolute references are created in the video below.

You can usually spot the cell that needs to be an absolute reference. It is usually a cell by itself. One huge advantage of using absolute reference is you don’t have to edit cells when changing numbers. This is shown in the video above in the first minute.

Here is the Microsoft article regarding absolute reference.

Chris Menard

Chris Menard is a Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 900 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 15 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube

Categories