What does $ (dollar sign) mean in Excel Formulas?

Reference cells correctly when writing Excel formulas. If your formulas are not working correctly when you copy them to other cells, you've probably made a mistake with your cell referencing. Some cell references might need to be fully fixed, some partially fixed. This is called absolute versus relative cell referencing in Excel. Understanding this concept really simplifies your Excel work.

The power of Excel lies in the fact that you can use these cell references in other cells when creating formulas.

Now there are 2 types of cell references that you can use in MS Excel:

• Relative Cell References

• Absolute Cell References

Lets how each Cell References will work in given examples:

Relative Cell References

Let me take a simple example to explain the concept of relative cell references in Excel. By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.

Suppose I have a data set shown below:

To calculate the total for each item, we need to multiply the price of each item with the quantity of that item. For the first item, the formula under "Total Column (E3)" would be C3* D3 (as shown below):

Now, instead of entering the formula for all the cells one by one, you can simply copy cell E3 and paste it into all the other cells by dragging down. When you do it, you will notice that the cell reference automatically adjusts to refer to the corresponding row. For example, the formula in cell E3 becomes C3*D3 and the formula in E4 becomes C4*D4.

When to Use Relative Cell References in Excel?

Relative cell references are useful when you have to create a formula for a range of cells and the formula needs to refer to a relative cell reference. In such cases, you can create the formula for one cell and copy-paste it into all cells.

Absolute Cell References

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.

An absolute reference is designated in a formula by the addition of a dollar sign ($) before the column and row.

For example, suppose you have the data set as shown below where you have to calculate the Bonus for each item’s total sales.

The Bonus is 25% and is listed in cell G2 as shows below:

To get the bonus amount for each item sale, use the following formula in cell E2 and copy for all others cells:

Note that there are two dollar signs ($) in the cell reference that has the commission – $G$2 (Press F4 as shortcut key to insert two dollar signs).

A dollar symbol, when added in front of the row and column number, makes it absolute (i.e., stops the row and column number from changing when copied to other cells). For example, in the above case, when I copy the formula from cell E2 to E3, it changes from =D2*$G$1 to =D3*$G$1. Note that while D2 changes to D3, $G$1 doesn’t change. Since we have added a dollar symbol in front of ‘G’ and ‘1’ in G1, it wouldn’t let the cell reference change when it’s copied. Hence this makes the cell reference absolute.

Here is what happens when you select the reference and press the F4 key.

• Press F4 key once: The cell reference changes from A1 to $A$1 (becomes ‘absolute’ from ‘relative’).

• Press F4 key two times: The cell reference changes from A1 to A$1 (changes to mixed reference where the row is locked).

• Press F4 key three times: The cell reference changes from A1 to $A1 (changes to mixed reference where the column is locked).

• Press F4 key four times: The cell reference becomes A1 again.

Keep Learning & Keep Thriving

Mohammed Alfan