Question

Explain those cell reference where $(dollar) symbol is used with example​

Answer 1

Answer:

Shift+4

Explanation:

Answer 2

Answer:

A dollar sign in Excel is used for absolute cell referencing. This tells Excel that you want to always refer to a specific cell even when your formula is copied across.

Let me explain:

What is cell referencing?

A cell reference is simply telling Excel the location of the cell (or cells) you want to use in your formula.

There are two types of cell referencing: relative cell referencing and absolute cell referencing.

As pictured below, in cell B5 we have the value of 10. The formula in B5 is =A5*B4

This formula is telling Excel we want to multiply the value in A5, which is 10, with the value in B4 which is 1 (10 x 1 = 10)

Notice there is no dollar sign used in this formula – this is called a relative cell reference.

So what happens when we copy this formula across a range of cells?

As pictured below, in cell C5 we have the value of 20. The formula in C5 is =B5*C4

This formula is telling Excel we want to multiply the value in B5, which is 10, with the value in C4 which is 2 (10 x 2 = 20)

Notice that after copying the formula, it no longer points to column A. Instead, Excel has copied the relative formula style: the cell to the left multiplied by the cell above (highlighted in red).

If you want your formula to always refer to a specific cell, e.g. cell A5, when it is copied across, you need to use absolute cell referencing.

This is where the dollar sign comes in.

In cell B5, we will use the formula =$A$5*B4

Adding a dollar sign in front of both the A and 5 that make up cell A5 sets both Column A and Row 5 as absolute.

When we copy the formula across, it will now always refer to cell A5

As pictured below, we can see that cell C5 now contains the formula =$A$5*C4

You can set rows and columns to be absolute depending on your needs. Here’s a handy guide from the GoSkills Excel course:

A1: Both the column and row will change.

$A1: The column will always point to A, but the row will change.

A$1: The column will change, but the row will always point to 1.

$A$1: Both the column and row will remain pointed to A1.

Keyboard shortcut: Toggle the $ signs on a reference when you are creating a formula by pressing F4 when your cursor is next to (or within) a cell reference.

If you’d like to learn more, check out this Cell Referencing tutorial. It includes an exercise file and solution so you can practice until you are confident with your skills.

Hope this helps!