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.
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% 🛒
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.
Nice. You get a $100 discount on Laptops 💻
Now if you drag the same formula down the whole list, this will happen.
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.
Excel will convert it into an absolute reference 🤩
Step 5) Drag this formula down the list of items.
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 🔢
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.
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).
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).
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 SpreadsheetoStep 4) Now drag the formula towards the right.
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:
Written by Kasper Langmann
Hi, I'm Kasper Langmann👋
I'm the co-founder of Spreadsheeto, a certified Microsoft Office Specialist, and a Microsoft MVP.
With over 10 years of experience, I’ve taught Excel to millions of people worldwide.
I spent over 13 hours researching and writing this tutorial.
Last updated on August 29th, 2024.
Before you go, sign up for my free Excel course (+100,000 students) ->
