Excel shortcuts

How to Use the Absolute Reference Shortcut in Excel

If you work with Microsoft Excel on a regular basis, you know that one of the most tedious and time-consuming tasks is having to constantly update cell references when you insert or delete rows or columns. This is especially true if your formulas contain a lot of cell references. For example, let's say you have a formula in cell A1 that references cells B1:B5. If you insert a row above row 5, you would then have to update the formula in cell A1 to reference cells B1:B6. This can be a real pain, especially if you have a lot of formulas that reference other cells. Fortunately, there is a shortcut that you can use to create what is called an "absolute reference." An absolute reference is a cell reference that will not change, no matter how many rows or columns you insert or delete. In other words, it is "absolute."

The shortcut for creating an absolute reference is to put a dollar sign ($) before the column letter and row number in the cell reference. For example, the absolute reference for cell B1 would be $B$1. The absolute reference for cell B5 would be $B$5. And the absolute reference for cell B6 would be $B$6. As you can see, the column letter always has a dollar sign in front of it, and the row number always has a dollar sign in front of it. This makes the reference "absolute."

Let's see how this works in a real example. Suppose you have a formula in cell A1 that references cells B1:B5. The formula might look something like this:

=SUM(B1:B5)

Now, let's say you insert a row above row 5. The formula in cell A1 will now reference cells B1:B6, and the formula will now look like this:

=SUM(B1:B6)

If you use absolute references in your formula, the formula will not change, no matter how many rows or columns you insert or delete. The formula will always reference the same cells. In our example, if we use absolute references in our formula, the formula will look like this:

=SUM($B$1:$B$5)

Now, no matter how many rows or columns you insert or delete, the formula will always reference cells B1:B5. The only time you would have to update the formula is if you actually changed the cells that the formula is referencing.

One final note: When you use the absolute reference shortcut, you don't have to put a dollar sign in front of both the column letter and the row number. You can just put a dollar sign in front of the column letter or the row number, depending on what you want to make "absolute." For example, if you want to make the column letter absolute but not the row number, you would use a reference like this: $B1. This would make the column letter B absolute (it would not change, no matter how many columns you insert or delete), but the row number would change if you inserted or deleted rows. Similarly, if you wanted to make the row number absolute but not the column letter, you would use a reference like this: B$1. This would make the row number 1 absolute (it would not change, no matter how many rows you insert or delete), but the column letter would change if you inserted or deleted columns.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.