The Advanced Guide To: What Does $ Mean In Excel?

Are you wondering what $ means in Excel?

It is true that the dollar sign has a wide range of meanings and uses within the Microsoft Office suite, including in Excel.

In this blog post, we'll explore this ubiquitous symbol by looking at its many uses. After all, you might encounter it when working with formulas in Excel spreadsheets. So, we'll provide some examples to help illustrate the concept further.

Read on as we cover the following:

• What Does $ Mean in Excel?

• Excel Cell Reference

• Relative Cell Reference

• Absolute Cell Reference

• Refer to an Entire Column or Row

• Mix Entire Column Reference

• Final Thoughts on What Does $ Mean in Excel

• Frequently Asked Questions on What Does $ Mean in Excel

Read Also: How to Search in Excel Spreadsheet

What Does $ Mean in Excel?

Many people need clarification when seeing a $ sign in the cell references formula. It is pretty simple to understand. The dollar sign represents the Excel cell reference used in formulas. You can choose whether the program should change this reference when copying formulas to other cells.

Check out our "Beginners Guide: How To Change Default Currency In Excel" blog post to know the other meaning of the dollar sign in Excel. 

Excel Cell Reference

The cell reference is the cell address, which Microsoft Excel tells you where to find the value you need.

For example, if you type "=A1" into cell D1 in your spreadsheet, it will replicate the value from cell D1 into A1.

The dollar sign ($) does not matter when writing a formula for one cell. The answer will be the same either way.

Example 1:

Example 2:

Example 3:

If you want to copy the formula over the worksheet, you must choose the correct reference type to get copied correctly to other cells.

Relative Cell Reference

In Excel, a relative reference refers to a cell address that doesn't have the $ sign in its row and column coordinates. For example, A1 is a relative reference.

If the formula replicates to another cell, the Relative cell references in Excel formulas will switch based on the relative location of rows and columns. Note that all references in Excel are relative by default.

For an example of how relative references work, refer to the following:

Let's say you have the following formula in cell C1:

=A1*2

If you replicate this formula to another row in the same column, say to cell C2, the formula will alter for row 2 (A2*2). Excel concludes that you want to multiply a value in each column A row by 2.

If you copy a formula containing a relative cell reference to another column on the same row, Excel will adjust the column reference accordingly.

When you copy or move a formula from one row and column in Excel to another, the numbers in the formula will change.

Using relative cell references is a convenient way to perform uniform calculations throughout the worksheet.

Absolute Cell Reference

An absolute reference in Excel is when a dollar sign ($) merges into the row's cell address or column coordinate like $B$2.

Using the dollar sign in cell references ensures that the reference to a particular cell stays the same, especially when relocated to another cell. Therefore, absolute cell reference means copying the formula in Excel will keep the cell references unchanged.

If you use an absolute cell reference ($B$2) and have a value of 50 in that cell, the formula =$B$2+10 will consistently result in 60, irrespective of the cells to which you copy the formula.

If you use a relative cell reference (B2) when writing a formula and then copy it to other cells in the column, the relative cell reference will calculate a unique value for each row.

An absolute reference in Excel generally remains the same. However, it changes when you modify the worksheet by adding or deleting rows or columns. Doing so can alter the location of the referenced cell.

For instance, if you insert a row at the beginning of the worksheet, the program adjusts the formula automatically to account for the change.

It is common to rely only on absolute references in Excel formulas in actual worksheets. However, as the following examples illustrate, many tasks require absolute and relative references.

Read Also: The Only Guide You Need About How To Multiply In Excel

How to Use Both Relative and Absolute Cell References in a Single Formula

Sometimes, you need a formula that adjusts cell references based on the destination while keeping specific cells fixed. This process involves combining relative and absolute cell references within a single formula.

The formula =B3*$C$1 contains two types of cell references:

The B3 cell reference is a relative reference that changes based on the row it is in. Meanwhile, the $C$1 cell reference is absolute and does not change when you copy the formula to different locations.

To always refer to the same cell in Excel, use an absolute reference by adding a dollar sign ($) to your formula when creating a static cell reference.

Refer to an Entire Column or Row

Using Excel with many rows, you can refer to all the cells in one column. To do this, type the column's letter twice and put a colon in between - like B: B.

Entire Column Reference

A complete column reference can be an absolute, relative, or cell reference. For instance:

• Absolute column reference "$B:$B."

• Relative column reference "B: B."

To lock a certain column for an entire column and prevent it from changing when copying a formula to other cells, use the dollar sign ($) in an absolute column reference.

Relative column reference in a formula will adjust based on its new position when copied or moved to other columns. At the same time, it will stay the same when copied to other cells within the same column.

Entire Row Reference

Enter the row numbers instead of column letters to cite all the cells in a row. For example:

• Absolute row reference "$2:$2"

• Relative row reference "2:2"

Entire Column Reference Example:

To find the total and average numbers in column B, you can reference the entire column B instead of using a fixed range of cells in an AVERAGE or SUM function.

To lock the formula to column B and make an absolute whole-column reference, use the dollar sign ($) before the column letter in the formula "=SUM($B:$B)."

To create a formula with a relative whole-column reference that will update as you copy it to other columns, write "=SUM(B: B)" without including the "$" symbol.

Note: To add an entire column reference to a formula in Excel, click on the column letter. By default, Excel will insert a relative reference without the $ sign, just like with cell references.

Similarly, we can create a formula to determine the average salary for all data within column B.

When using a formula in Excel with an entire-column reference, don't put it in the same column. For example, don't put the formula =SUM(B: B) in one of the empty cells at the bottom of column B, which won't work and will give you a wrong answer, 0.

Entire Row Conference Example:

First, to calculate the average pay in an Excel sheet's row, sort the data in rows rather than columns. For instance, in row 4, you can reference the entire row in your formula.

• The formula "=average($2:$2)" calculates the average of all the cells in Row 2. The dollar sign before the row number ($2) locks the reference to Row 2 and makes it an absolute reference.

• The formula "=average(2:2)" will adjust using a whole-row reference to different rows.

To learn more about the AVERAGE function, check out our "Basic Microsoft Excel Formulas You NEED to Know" blog post!

Mix Entire Column Reference

As mentioned, you can reference an entire column or row in Excel using a cross-reference.

• Mixed column reference ($B: B)

• Mixed row reference ($2:2)

Be careful not to use too many references to entire columns or rows in your Excel worksheet, as it can slow down your Excel spreadsheet program.

Final Thoughts on "What Does $ Mean in Excel?"

In conclusion, the dollar sign ($) in Excel is a useful tool that can make complex calculations seem effortless.

It serves as shorthand to denote absolute cell references or apply currency formatting quickly. Such a function helps if you are trying to do something specific in the context of your spreadsheet, like creating a financial budget or model.

Sign up for a Simple Sheets subscription plan and access 100+ Excel templates! For more easy-to-follow guides, visit Simple Sheets and the Related Articles section of this blog post. 

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

Frequently Asked Questions on What Does $ Mean In Excel.

What is the default reference style I can use?

The default reference style used by Excel for the Web is A1, which means that columns are in letters and rows by numbers. These are row and column headings, which refer to a specific cell.

How can I change between mixed, relative, and absolute references with F4?

To change references, do the following steps:

1. Select a cell with a formula.

2. Double-click or press the F2 key.

3. In the Edit mode, choose a cell reference you want to switch.

4. Use the F4 key to switch between kinds of cell references.

How can I calculate dates with relative and absolute cell references?

If you have a list of delivery dates in column A and want to calculate how many days each item takes to dispatch, you can use the following formula: =A1-$B$1. To use this formula, you should input the current date in cell B1 using the TODAY function.

Related Articles:

Excel Remainder: How to Get it With the Excel Mod Function

The Excel IFERROR Function: What is it and When to Use it

User-Defined Functions in Excel