Using Dollar Signs ($) To Create Absolute And Relative References In Excel

Table of Contents[Show]
Using Dollar Signs ($) To Create Absolute And Relative References In Excel


Have you ever seen the dollar symbol ($) in the cell reference / range of an Excel formula? What is the purpose or function of the dollar sign ($) in Microsoft Excel?

This excel tutorial will discuss how to lock excel formula references or create excel absolute formulas using the $ (Dollar) sign.

Relative Cell Reference in Excel

By default, a cell reference or range in Microsoft Excel is relative. Relative references to an Excel formula will change accordingly when we duplicate (copy-paste) it from one cell to another.

For example, when we refer to cell C1 from cell E5, we are actually referring to a cell that is two columns to the left and four rows up.

Relative cell reference in Microsoft Excel

 

Absolute Cell Reference in Excel

If we want to lock the excel formula or keep the original cell reference from changing when we copy ( Copy-Paste ) it to another cell, then we need to make the cell reference absolute by prefixing the column (C) and row (1) addresses with a dollar sign ( $) so that the formula becomes:


=$C$1

 

If we copy the formula (= $ C $ 1) in cell E5 to F5 for example, then the formula will remain exactly the same. Such a reference is called an (Absolute reference) or (Absolute Cell).

Absolute Cell or Absolute Reference is the term for a cell whose address is locked. The purpose of this locking is so that the cell address does not change when the Auto Fill or Copy paste process is carried out.

If you still don't understand about Auto Fill, you can read the discussion about Auto Fill in Excel .

With this Absolute Cell, Auto Fill and copy paste can still be used in a semi-consistent data table or in advanced formulas that use reference tables.

Semi Absolute Cell Reference in Microsoft Excel

In certain cases sometimes we also want only columns to be absolute or rows to be absolute. Such references are called mixed references or semi-absolute references .

If you want absolute columns then add a dollar sign ($) in front of the column names. Example:


=$C1

 

If what we want absolute is only the line then add a dollar sign ($) in front of the line number. Example


=C$1

 

So the function of the $ sign in Excel formulas is to make cell addresses or data ranges in Excel formulas relative to absolute and semi-absolute formulas.

Simply put, if the excel formula before the column has a $ sign, when the column is copied it does not change, as well as if the address row has a dollar sign ($). then the rows will not change when copied to other cells. 

How to Use the $ (Dollar) Sign in Excel

As explained above that to create absolute cells use the $ (Dollar) sign on the cell reference / Range.

In addition to writing dollar signs ($) manually, absolute references can be generated by pressing the function key F4 on the keyboard. The trick is to mark the cell reference or range that we want to change the type and then press the button F4 on your keyboard.

At this point we should already know three types of cell references. Namely, relative cells , absolute cells and mixed or semi-absolute cells .

Look at the table below:

= A5 Relative Reference Example
= $A$5 Absolute Reference Examples
= $ A5 Example of Mixed / Semi Absolute Reference (Relative Row Absolute Column)
= A $ 5 Example of Mixed / Semi-absolute Reference (Absolute Row Relative Column)

Also pay attention to the picture below:

Excel Absolute and Relative Reference

 

If you look at the picture above, we can know that:

  • By using a $ prefix in the cell address, the duplication of the cell will not change the cell address.
  • = A1: Columns and Rows can change when copy and paste or auto fill.
  • = A $ 1: Column A is subject to change, but the address line 1 will remain (absolute).
  • = $ A1: The address column A cannot change (absolute) but the address line 1 can change.
  • = $ A $ 1: The address of column A and row 1 will not change when duplicated to another cell.

 

Apart from manually adding a $ sign in front of the column name or row number, we can also use the keys F4 on the keyboard to switch between reference types.

 

Using absolute references is helpful for example to lock table references in the arguments of the Vlookup function or when using excel Hlookup formulas .

This is to ensure that we only have to write an excel formula once and no longer need to readjust it when we copy and paste the formula to another place.

Apart from using a dollar sign or creating an absolute reference we can also make an address range immutable when copied by naming the range or cell. 

This is the tutorial article about Absolute Reference or the use of the $ sign in Excel. Hopefully it can be useful for all of us.

Before leaving, don't forget to bookmark and share this excel tutorial article with the buttons provided below. Greetings Excel Class.


Leave a Comment