What does the dollar sign mean when used in a range in the formula bar in Excel?
Occasionally you may want to use the dollar symbol in an Excel range. Take a look at this example to see why. In one column we have the temperature for a given day – spanning a range of 10 days. Now, let’s say we want to have a column that represents how many degrees below the max that particular day’s temperature is. So, as you can see in the screenshot below, we come up with the formula “=MAX(B3:B12)-B3” for cell C3 – translated in English that formula means “find the maximum value in between cells B3 and B12 and then subtract the value in B3 from that maximum value, and that is the value we want for cell C3”. Here’s what it looks like:
Now, if we drag cell C3 down to cover cells C4 to C12, let’s see what happens:
Pay special attention to the formula that appears in cell C12 – note that it says “=MAX(B12:B21)-B12”. This means that Excel is automatically updating the range to use cells that have the same relative position of cell C3 to cell C12 – since there is a difference of 9 cells between C12 and C3, Excel updates the range from “=MAX(B3:B12)-B3” to “=MAX(B12:B21)-B12”. But, this is not what we want – we need to fix the range of cells that are being referenced so that it always uses cells B3 to B12.
How can we fix the range of cells being referenced? Well, we can actually use the dollar symbol to “fix” the reference to the range of B3 to B12. So, this is the formula we want to use in cell C3: “=MAX($B$3:$B$12)-B3”. Note that we don’t fix the “B3” reference because we want Excel to automatically change that cell reference when cell C3 is dragged down. Here is what the spreadsheet looks like when the correct formula is placed in cell C3:
You can see that the formula for cell C12 is ” =MAX($B$3:$B$12)-B12″ – so the reference to the range of cells B3 to B12 is fixed, but the portion “- B12” has been changed from “- B3” because that cell reference has not been fixed through use of the dollar sign, and that is exactly what we want.
Partially fixed ranges in Excel
We just went through an example of fixing an entire range of cells. But, what about partially fixing a range of cells in Excel? An example will help clarify what we mean by that. Suppose we have one column that represents the money made by a business on a given day in a month. Next to that column, we want to have a column that represents the total money made for that month up to that day. But the question is how to write the formula for that particular column to calculate the cumulative sum. So, here is what we have so far:
The question now is what formula should be in the “Revenue Month to Date” column. Try this out for yourself to see if you can come up with the answer.
As mentioned before, we will use a partially fixed range to come up with the answer. We need to use a range, but we also want that range of cells to change depending on what the current cell is. How can we do this? Well, we will clearly need to fix the first cell in the range, since that will stay the same no matter what.
What we need to do is make the range somehow end at the cell to the very left of the current “Revenue Month to Date” cell. That is actually very easy – the key here is that we just do not use the fixed reference for the cell at the end of the range. And then we simply sum all of the cells in the range. With that in mind, we can come up with this formula for cell C3: “=SUM($B$3:B3)”. The B3 cell reference will of course change when the cell is dragged down, because it is not fixed – this means that the cell which terminates the range will always be in the same row as the current cell in column C. That may sound confusing at first but think it over and it should make more sense to you. And finally, here is what the final product looks like:
As you can see in the “Revenue Month to Date” column, there is a cumulative sum of the revenue value shown in column B.