Skip to main content

Making sense of dollar signs in Excel

We’ve noticed some of you searching for help using “$” – a dollar sign. In Excel, a dollar sign can denote a currency format, but it has another common use: indicating absolute cell references in formulas. Let’s consider both uses of the dollar sign in Excel.

Microsoft Excel

Turn data into insights.

Get Excel

Dollar signs denoting currency

If you want to display numbers as monetary values, you must format those numbers as currency. To do this, you apply either the Currency or Accounting number format to the cells that you want to format. The number formatting options are available on the Home tab, in the Number group.

Accounting number format in Number group

What’s the difference between the two number formats? There are two main differences:

  1. The Currency format displays the currency symbol adjacent to the number, whereas the Accounting format displays the symbol at the edge of the cell, regardless of the length of the number.
  2. The Accounting format displays zeros as dashes and negative numbers in parentheses, whereas the Currency format displays zeros as zeros and denotes negative numbers by using a minus sign (-). For more information, see the article Display numbers as currency.

Dollar signs indicating absolute references

You probably know that a formula can refer to cells. That’s one reason Excel formulas are so powerful — the results can change based on changes made in other cells. When a formula refers to a cell, it uses a cell reference. In the “A1” reference style (the default), there are three kinds of cell references: absolute, relative, and mixed.

Absolute cell references

When a formula contains an absolute reference, no matter which cell the formula occupies the cell reference does not change: if you copy or move the formula, it refers to the same cell as it did in its original location. In an absolute reference, each part of the reference (the letter that refers to the row and the number that refers to the column) is preceded by a “$” – for example, $A$1 is an absolute reference to cell A1. Wherever the formula is copied or moved, it always refers to cell A1.

Relative cell references

In contrast, a relative reference changes if the formula is copied or moved to a different cell (i.e., a cell other than where the formula was originally entered). The row and column portions of a relative reference are not preceded by a “$” –  for example, A1 is a relative reference to cell A1. If moved or copied, the reference changes by the same number of rows and coulmns as it was moved. So, if you move a formula with the relative reference A1 one cell down and one cell to the right, the reference changes to B2.

Mixed cell references

A mixed reference uses a dollar sign either in front of the row letter or in front of the column number, but not both – for example, A$1 is a mixed reference in which the row adjusts, but the column does not. So if you move a formula containing that reference one cell down and one cell to the right, it becomes B$1.

Which kind of cell reference should you use?

The kind of cell reference you use depends on what you are doing, but usually you want to use relative references. Excel uses relative references by default, which makes it easy to fill formulas down and across: the references automatically update, which is what you want, most of the time.

One case where you might want to use an absolute reference is when using the VLOOKUP function. Though the value you’re wanting to look up might change (for example, as you fill the VLOOKUP formula down a column), the actual location of the lookup table doesn’t change or adjust as you fill the column down.

But now that you know the differences between these kinds of cell references, you can make the decision for yourself, based on how you want your formulas to behave. Do you have some use-cases to share with the community? Share them by leaving a comment!

— Steven Thomas

Related posts

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…

Image for: Surface Pro X and Surface Arc Mouse inside on desk with Excel and search on screen.

Connect to your own data with more new data types in Excel

The evolution of Excel Excel is the ultimate decision-making tool. We’re amazed every day by the ways in which you, our customers, use Excel to make better decisions, leveraging the flexibility of the 2D grid and formulas to capture, analyze and collaborate on data. Up to this point, Excel has only had a couple base…