What does the dollar sign ($) mean in Excel formulas? ($A$1)

After running Excel courses for 25+ years there are certain features of Excel that regularly provide an “ah ha” moment for participants. This is one of those features and I usually start with a simple question: “Have you seen dollar signs in formulas and ever wondered what they are for?”. The answer is almost always “Yes!”.

So today I want to explain what the dollar signs in an Excel formula mean and how you can use them to your advantage.

Relative & Absolute cell references explained

Excel uses two different types of cell references: RELATIVE and ABSOLUTE. The type of cell reference used will dictate how the cell reference behaves if you copy or AutoFill the formula to another location within the workbook.

Common question’s I hear include: “Why does my formula change when I copy it?“, or on the opposite side of that “How do I stop my cell references changing when I copy them elsewhere?“.

This is where having a good understanding of the two different cell reference types can help you achieve the outcome you are after when using a cell reference in a formula or function.

Another aspect of absolute cell references if the fact there are three (3) different ways you can lock the cell reference.

1. Lock both the column & row: The column and row do not change when copied to other locations.
2. Lock the row only: The row number does not change, but the column letter will change when copied. This is called a mixed cell reference. See section below for more details.
3. Lock the column only: The column letter does not change, but the row number will change when copied. This is also called a mixed cell reference. See section below for more details.

Use a relative cell reference

Now that you understand the two different types of cell references, let’s look at these in action and see what happens when we copy a formula containing a relative cell reference.

1. Open Microsoft Excel.
2. Create a new blank workbook or open an existing workbook with suitable data to work with.
3. In the worksheet add a Price column and Quantity column. Add some sample values such as those shown below:

4. Place your cursor in the first cell of the next column to create a simple multiplication formula. E.g. click in D3.
5. Enter the formula =B3*C3.
6. Press Enter to display the answer:

7. To copy the formula to the rest of the cells in the column, select cell D3 and press Ctrl + C to copy.
8. Click in the next cell in the column, D4, and press Ctrl + V to paste.
9. Alternatively you can use AutoFill to copy the formula down the entire column.
10. Select the next cell and look at the Formula bar to see the formula. It should have automatically changed to be =B4*C4:

11. Each formula in the column (after copying the formula to all cells) will have automatically adjusted the row number down by one, each time it is copied to the next row.

This is a simple example of how relative cell references work by automatically adjusting cell references where you want to repeat the same formula for multiple rows or columns.

Use an absolute cell reference

Now let’s look at an example where we DON’T want the cell references to change when they are copied or AutoFill is used.

1. Add a new worksheet to the workbook.
2. In the worksheet add a Sales column and Commission Payable column. In a cell on row 1, add a Commission rate value as a percentage. Add some sample values:

3. Select cell C4.
4. Enter the formula =B4*F1.
5. Press Enter to display the answer:

6. Now try using Copy & Paste, or AutoFill, and copy the formula down the rest of the column.
7. Every other record is displaying a zero value!

8. The formula has not worked for the rest of the data.
9. Click into the first cell with an incorrect value of zero. Look at the Formula bar and inspect the formula. It says =C5*F2.
10. The C5 reference is correct as it’s been changed to look at the sales figure in column C and on row 5, however the F2 reference is wrong as it is pointing to a cell with NO DATA. The formula is therefore multipying the sales value by an empty cell which gives a zero value.
11. This is where we need to use an Absolute cell reference which will remain locked in and will not change when we copy the formula elsewhere.
12. Even though the first formula we entered has worked correctly, we are still going to edit it so that moving forward all cell references in this column are using an absolute cell reference.
13. Click back in cell C4.
14. Click into the Formula bar and ensure the cursor is near the F1 reference:

15. Press the Function F4 key on the keyboard.
16. Excel will automatically lock the cell references by adding the $ symbols before both the column letter and row number. This locks in the column letter AND the row number:

17. Press Enter to complete the edit.
18. Copy the new formula over the previous version of the formula for the rest of the column.
19. All cells will now display a correct commission value based on sales and the 5.7% commission rate:

20. You can now edit the commission rate in cell F1 at any time and each formula will refresh to display the updated answer.
21. Repeat the process for the February sales commissions.

Understanding the difference between relative and absolute cell references can save you a lot of frustration when building formulas in Excel. Once you know when to allow a cell reference to change and when to lock it in place using the dollar sign ($), you’ll be able to copy formulas with confidence and create spreadsheets that are more accurate and easier to maintain. It’s a small feature that often has a big impact, and one that every Excel user should have in their toolkit. If you’ve never paid much attention to those dollar signs before, hopefully now you can see just how useful they can be.

Feel free to leave any questions below or check out more of my Excel tips and tricks.