Quick Excel Tips to Boost Productivity Easily – Dollar Sign Referencing

A dollar sign is used in a Microsoft Excel formular to create absolute references. By including dollar signs before a column, a row, or before each of the two, you are instructing Microsoft Excel to always refer to that specific cell. The alternative is a relative reference which changes the reference cell in any subsequent application of the forumular.

What It Does

When you reference a cell, say F2 in another cell, say C2, you are in essence referring to a cell that is three steps away from your current location (C – F). Say you use this reference in a formular and apply that formular to another cell (like C3), the F2 reference will change so that it still references a cell three steps away. In this case, the formular will reference F3 because it is also three steps from C3. This is known as relative referencing, and is the default referencing type for Microsoft Excel.

How It Is Used

Depending on your needs, you might want to use an absolute or relative reference model in your formular. The example below helps recreate a dollar reference to use in a typical referencing formular. The table below shows the prices of groceries. We also have the Value Added Tax (VAT), National Health Insurance Levy (NHIL), as well as other levies. In the formular bar, =B2+(B2*F2) computes the price of milk inclusive of the VAT levy. The expression in bracket computes the VAT levy as a percentage of the initial price of milk; adding this value to the initial price of milk gives you the final price inclusive of the VAT levy.

A typical relative referencing headache unfolds as follows. Say you would like to compute the price of sugar too with VAT inclusive. One way of doing this is to enter the formular all over again in the respective cell. You would need to be careful to not omit or add any value in order to get the correct result. To avoid this possibility, point your mouse to the bottom right of cell C2. Your mouse pointer will get turned into a plus sign (+). Click and hold on to that cell, and then drag it to the cell below. This process will replicate the previous formular into cell C3. Doing that will give you the following:

Immediately you can see an error in the formular. Instead of multiplying B3 with F2 to give you the price of sugar inclusive of VAT, Excel multiplied B3 with F3 to give you the price inclusive of NHIL. In the formula bar, you can see, =B3+(B3*F3) . This type of referencing can lead to serious errors that could affect your entire data analysis. It is for this reason that you should use a dollar sign to maintain an absolute reference.

As you can see in the above diagram, the $F$2 in the formular bar indicates that we are still using F2. With this kind of referencing, we are still computing the right price for the grocery item. The dollar signs in =B3+(B3*$F$2) ensures the reference point is maintained throughout our forumular. This is called Absolute Referencing.

It is also possible to use mixed referencing in the application of your formular. Entering a $ in front of the letter alone keeps the column constant while formatting the cells. A $ before the number keeps the row constant. Good knowledge of this Microsoft Excel formular will keep you on top of all your cells.