How to Keep a Cell Constant in an Excel Formula

Excel is known for its ability to handle complex formulas. And once you’ve put in all your brain to write up a formula, you can drag the fill handle to take it across multiple rows and columns in less than a second.

But hold on! Before you begin playing around with Excel formulas like a pro, make sure you know what goes behind them.

Understanding cell references and constants in Excel is crucial because they determine how formulas interact with data. Knowing how to properly reference cells can prevent errors, save time, and ensure the accuracy of calculations, especially when copying formulas across different parts of a spreadsheet 🤔

In this guide, I will show you how to keep a cell constant in MS Excel so that when you copy a formula to another cell, the reference to a specific cell remains unchanged. This is done using absolute references.

What are these and how do you work with them – we will cover that all below so grab your free practice workbook for this guide now and read till the end.

Understanding Cell References in Excel

Cell references in Excel identify the location of a cell in a worksheet.

For example, the cell reference A1 tells that Cell A1 is situated at the intersection of Column A and Row 1. While you store data in cells in Excel, you can also use their references in formulas to retrieve data from those cells 🚴‍♂️

There are three types of cell references in Excel:

  • Relative References: Change when a formula changes cells.

For example, if you move the formula [=A2 + B2] from cell C1 to D1, it will change to [=B2 + C2].

  • Absolute References: Remain constant irrespective of where they are moved.

Add a dollar sign before the column and row reference to turn a relative reference (A1) into an absolute reference ($A$1).

  • Mixed References: A combination of relative and absolute references (either the column reference is preceded by a dollar sign or the row reference). As the formula changes cells, the relative reference updates but, the absolute reference remains constant.

For example, $A1 is a mixed reference where column A is fixed but the row reference is relative.

Pro Tip!

Dollar signs are used to create absolute references.

Adding a dollar sign ($) before the column letter (e.g., $B1) the row number (e.g., B$1), or both (e.g., $B$1) will turn that part of the cell reference constant even when the formula changes cells.

Keeping A Cell Constant in an Excel Formula

Let’s now quickly look into an example of why you need to keep a cell constant in an Excel formula and how can you do it.

Here I have a list of items together with their original price. Each of these items is subject to a discount of 10% 🛒

Items and their prices

So, what is the price at which I get them?

Let’s calculate.

Step 1) Multiply the price of the item by the discount percentage.

Click to copy
Multiplying item price to relative cell reference

Nice. You get a $100 discount on Laptops 💻

Now if you drag the same formula down the whole list, this will happen.

Dragging formula across range of cells

This is because, as you drag the formula down, the cell reference for the prices changes but at the same time, the cell reference for the discount percentage also changes. However, we only want the cell references for the prices to update and that for the discount to remain constant.

To help this, we will convert the cell reference for discount percentage into an absolute reference.

To convert the cell reference for cell B9 into an absolute reference:

Step 2) Double-click on the cell containing the formula.

Step 3) Take your cursor to the cell reference for B9.

Step 4) Press the F4 key on your keyboard.

Pro Tip!

Pressing the F4 key while editing a cell reference in a formula toggles between relative, absolute, and mixed references.

For example:

  • Pressing the F4 key once will convert B9 into $B$9.
  • Pressing the F4 key twice will convert $B$9 into B$9.
  • Pressing the F4 key thrice will convert B$9 into $B9.
  • Pressing the F4 key for the fourth time will convert $B9 into B9.

Alternatively, you can insert a dollar sign before the column letter and row number to convert it into an absolute or mixed reference (as needed).

Excel will convert it into an absolute reference 🤩

Click to copy
Absolute cell reference

Step 5) Drag this formula down the list of items.

Keep formula cell reference constant

There you have it. Excel calculates a 10% discount on all items by keeping the cell reference for the discount percentage constant.

Similarly, let’s see a quick example of using mixed references in Excel.

Here, I have a matrix of numbers in Excel 🔢

Matrix of numbers

If I want to add the number in Row 2 to each of the numbers in Columns B, C, and D, here’s the formula I write.

Click to copy
Formula to add referenced cells

However, as you drag it towards the right, the column reference for the numbers in Column A will also change (that we don’t want).

Column reference change

Step 1) Launch the formula.

Step 2) Take your cursor to the reference for Cell A2.

Step 3) Press the F4 key twice or add a dollar sign before A to convert this cell reference into a mixed reference ($A2).

Converting A2 into a mixed reference

This might not be as important in this example as we are more focused on keeping the Column reference constant. However, otherwise, in a mixed reference, the column or the row reference is fixed with a dollar sign but the other reference is left relative. This ensures that as you copy the formula down, the relative reference (say row number) changes, but the absolute reference (say column) remains fixed.

Kasper Langmann, co-founder of Spreadsheeto

Step 4) Now drag the formula towards the right.

Column reference doesn’t change after first cell

The column reference for Column A won’t change.

These are just two examples of why it is important to keep a cell constant in an Excel formula. There is so much more than you can achieve by keeping certain cells constant in Excel.

Conclusion

This tutorial is dedicated to a comprehensive understanding of different types of cell referencing in Excel. From relative reference to mixed to absolute references – we’ve seen them all.

More importantly, we have seen practical applications of when and why you need to keep a cell reference within a formula constant. It is easy to toggle between different types of cell references that you can set up per your needs 🥂

If you enjoyed learning these techniques, make sure you don’t stop just here. There are more than 300 blogs on Spreadsheeto that cover interesting features and functions of Microsoft Excel together with Excel tips. Some of the related articles that I suggest you check out are these: