Trending September 2023 # Dollar ($) In Excel – Why And How To Use $ (Formula) # Suggested October 2023 # Top 11 Popular

You are reading the article Dollar ($) In Excel – Why And How To Use $ (Formula) updated in September 2023 on the website Nhahang12h.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Dollar ($) In Excel – Why And How To Use $ (Formula)

Use of $ (Dollar) in Excel

$ The Dollar symbol/strong in Excel is used in a cell address/reference to lock the reference to that specific row or Column and not let it change when we copy the Formula to a different cell or if we drag the cell with a formula horizontally or vertically

To explain,

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

You will understand this better when we walk through what ‘Cell Address/Reference’ is and how Excel uses them.

What is Cell Address/Reference?

So, a cell address is Column Alphabet + Row Number.

A cell from Column B and Row 4 will have an address/reference as ‘B4‘.

The image below shows more cells and their addresses marked within.

Use Cell References in Excel

= A2+B1

How Does Excel execute Drag/Copy & Paste of formulas?

When a cell is selected, a small filled-in box appears at the lower right corner of the selected cell called the “Fill Handle”. The below picture illustrates it-

While selecting a series of cells, this Fill Handle makes it convenient to drag precisely to whichever cell is necessary, as shown below.

Let’s say there are two columns with numbers, and you want the sum of two adjacent cells in the third column’s corresponding cell.

This formula works as shown below.

What is Relative Cell Reference?

Instead of typing out the formula for each cell, Excel lets you drag the same formula down as far as calculations are needed. Excel will automatically change the cell references in the existing formula relative to where we drag it. Let’s see how this works.

See how Excel changes the references in the formula when the formula we drag from cell C2.

It can happen while we drag the formula across cells or if we copy the formula from one cell to another too.

Changing cell reference while copying formula from one cell to another:

Let’s copy the same formula, “=A2+B2,” from cell C2 as shown below.

We will copy it to cell C6 by selecting cell C2 using the keyboard shortcut Ctrl + C (for Windows) or Command + C (for Mac). Then we will move the cursor to cell C6 and use the keyboard shortcut Ctrl + V (for Windows) or Command + V (for Mac).

What is an Absolute Cell Reference in Excel?

What if you do not want Excel to change cell references when the formula is dragged or copied to a different cell? It is possible when you Lock the cell reference by using $ in Excel. Excel refers to a locked cell as an Absolute Cell Reference because we cannot change it. It is absolute!

How to Use Excel $ (Dollar) in Formula

Excel uses an absolute cell reference in a formula in the following way-

That is why we can infer this:

Use of an Absolute Cell Reference in a formula

We have used an absolute cell reference using $ symbols in the Excel formula, as shown below.

However, an absolute cell reference will never change, be dragged, or copied to a different cell.

=A2+$B$2

$B$2 is an absolute cell reference referring to cell B2 with a value of 1 in it. As you will see below, this will never change in the formula when dragged or copied.

What is a Mixed Cell Reference in Excel?

A mixed cell reference combines a relative and absolute cell reference. It gives us the choice of either locking the Column part of the address or the Row part.

A$1 – Locks Excel from varying the Row reference.

Use of a Mixed Cell Reference in a Formula

The following multimedia image shows when we drag down the same formula, the Row Number in the same formula changes because we have not locked it with a $.

Also, in the image below, notice how we have locked the Row Number with a $, so when we copy the formula to a different row, the Row reference does not change, but the Column reference changes because we have left it unlocked.

To summarize, we can use cell references in three ways:

Cell Reference Type

How does it behave?

Relative Cell Reference – No $, No lock

Example: A2

Will change when we copy or drag the formula to a different cell

Absolute Cell Reference – $, Locked

Example: $A$2

It will never change when we copy or drag the formula to other cells

Mixed Cell Reference – $, Partly Locked

Example: $A2 or A$2

The cell reference’s unlocked part, Column or Row, will change.

Examples

Let us work through an example for each cell reference type.

You can download this $ in Excel Template here – $ in Excel Template

Example #1 Relative Cell Reference

In this example, we’ll use a relative cell reference in a simple formula to calculate from a list of items. Attached here is an Excel worksheet that contains the list shown below.

The goal is to add the scores of three subjects – Math, Science, and English- for each student to calculate their final scores.

=B2+C2+D2

= B6+C6+D6

Thus, this is called Relative Cell Reference.

Example #2 Absolute Cell Reference

We have the price list of a grocery store. The grocery store has increased prices of certain items by 10%, and we will calculate the new price using absolute cell reference. The Excel sheet is shown below.

= B2+B2*$F$2

Notice how $F$2 is an absolute cell reference because, in this case, the % Increase in Price is a constant and should not change if dragged or copied.

The new price for a dozen Eggs will now be calculated in cell C2. As you did in the previous example, select cell C2, grab the fill handle, and drag it down to the end of the list.

Select a cell in the New Price column to see if the absolute cell reference in the formula has changed after we drag it to other cells.

It has not! All other relative cell references have adapted to the new location, while the absolute cell reference remains unchanged.

Example #3 Mixed Cell Reference

In this example, we will create a Multiplication Array. The intersection of a number on the column header and the row number should have the product of them. The intersection at 5 in the column and 4 in the row, i.e., cell F6 should contain the product of 5 and 4, which is 20.

It is a comprehensive example explaining mixed cell references thoroughly and copying and pasting the formula.

For this to work, the formula should combine mixed cell references. When we move along the same row, the formula should contain one component that changes. The column should change as we move to the next cell in the same row.

=$A3*B$2

What remains now is to drag/copy this formula across the entire array and check if the formula adapts as expected. But, before that, let us visualize how this formula is expected to change in the neighboring cells to understand mixed cell reference better.

The image below will explain how the formula is expected to adapt.

With every change in the cell, the column or row reference in the formula changes accordingly. This is how we can use mixed cell references.

All that is left is to drag the formula or copy and paste it as shown.

Pro-Tip

It is very convenient to use shortcuts to cycle between Relative, Absolute, and mixed cell references while typing a formula.

After entering the cell address as a relative reference in a formula, use the shortcut: For Mac – Command+T, For Windows – F4. The address will cycle between Relative, Absolute, and Mixed cell references.

Frequently Asked Questions (FAQs)

Answer: We can use the “$” sign in Excel for two purposes. Firstly, it can be useful in denoting the US currency – the $ (dollar). The $ (Dollar) symbol can lock in cell references in Excel formulas and functions. It helps us simplify the process of using the same cell range and formula at various places in the worksheet.

Answer: The three types of cell references available in Excel are: Relative, absolute, and mixed.

Answer: When the $ symbol comes before the column value “B,” it means that the column value will stay constant when we copy or drag the formula. On the other hand, when the $ sign comes before the row number, the row value will stay constant when we reuse the formula in another cell.