How To Keep a Cell Constant in Excel in 3 Steps (With Tips)

Updated June 24, 2022

Excel has many functions, formulas and formats that can help users create complex and comprehensive spreadsheets. A cell constant in a formula is a function that assists users when performing multiple calculations based on a consistent value. Learning this skill can help you save time and minimize errors when creating your own spreadsheets and calculations. In this article, we discuss how to keep cells constant in Excel, explain why you might learn this skill and list some tips for using constant cells.

Why keep a cell constant in Excel?

You can make cells constant when you want a reference in a formula to stay the same when using it in multiple cells. In Excel, formulas use different types of references. Relative references, which Excel uses by default, are cell references that use the relative location of one cell to another. This means the cells that the formula references shift if you apply the formula elsewhere. Absolute references refer to the specific column and row of a cell, which means the cell stays constant in the formula wherever you apply the formula. Absolute references keep cells constant.

Formulas often use an absolute reference to refer to a specific value for multiple calculations. You may choose to create your formula by inserting this value as an integer and copying the formula to each new location. Using an integer relies on the formula always staying the same. Creating a constant cell that contains the specific value allows you to change the value in the cell without editing the formula manually. This often helps when calculating things like discount prices with a rotating amount.

How to keep a cell constant in Excel?

Here are three steps you can take to keep a reference cell constant in Excel:

1. Decide which cell to keep constant

Whether you start a new spreadsheet or work with an existing one, determine which cell you want to stay constant and why. Select the cell you want to refer to and fill in the relevant data. For example, if you want to calculate prices, you might keep the cell that shows a tax or discount rate constant. If you want to determine profits, you might keep the cells with the costs constant.

2. Select the formula that uses the constant cell

To keep a cell constant, add the absolute reference to the formula that uses that cell. Once you have created or selected your desired formula in the spreadsheet, select the cell that contains the formula. This allows you to edit the formula in the formula bar.

3. Designate your constants in the formula

Excel uses dollar signs ("$") to designate absolute references in formulas. You can add the absolute reference to your formula using one of two methods:

Manual entry

The first method to keep a cell constant in a formula is to add the reference symbols manually. After selecting the cell with the formula, go to the formula bar and use your cursor to add "$" before both the column and row name of your constant cell. If your formula references cells D4 and E4, and you want E4 to stay constant, type the cell reference as "$E$4" in the formula.

Keyboard shortcut

The second method uses a keyboard shortcut to create the absolute reference. When using a computer with Windows, the "F4" key adds the reference. With the cell containing the formula selected, place your cursor in the reference between the column and row names and press the "F4" key. Some computers require you to press and hold the "Fn" key before pressing "F4". This shortcut adds "$" before both the column and row name at the same time.

You can also use a keyboard shortcut when using a Mac operating system. The shortcut "Command + T" adds the reference to the formula. As with Windows, select the cell with the formula and place your cursor in the middle of your reference term, then use "Command + T." The shortcut adds the "$" to both halves of the cell name. By adding the absolute reference symbols to your cell constant, you ensure that your reference cell stays constant when transferring or copying that formula to other cells.

Tips for keeping a cell constant

Here are some tips for using and creating constant cells:

Create mixed references

Excel uses relative references, absolute references and mixed references. A mixed reference combines the other two types and keeps either a column or a row constant. You can create a mixed reference by adding a "$" to either the row name or the column name, like "A$4" or "$A4." This allows you to apply a formula across a range of cells without manually editing the formula.

Sometimes, a formula will use two mixed reference terms, with one term keeping the row constant and the other term keeping the column constant. This often helps when calculating multiple values simultaneously.

Make ranges constant

Excel formulas can reference both singular cells and cell ranges. You can make a range constant the same way you make a cell constant. In the formula bar for the desired formula, add "$" to your range term. For an absolute reference to the range, for example "A2:A5," use either the manual or shortcut method to add "$" to all parts of your range, like "$A$2:$A$5." You can also create mixed references, keeping the column constant with "$A2:$A5" or keeping the row constant with "A$2:A$5."

Use the toggle function

The keyboard shortcuts "F4" and "Command + T" have a second function when making cells constant in formulas. After using the shortcut to add an absolute reference, you can then use the shortcut to switch the format of the references. Pressing the key two or three more times rotates your formula term through mixed references or relative references. For example, if your formula references D5 and you used the "F4" shortcut to create "$D$5," then pressing the key again will give you "$D5," "D$5" or "D5" in the formula bar.

Use the fill handle

A spreadsheet uses the same formula applied to several adjacent cells. You can manually type each formula into the formula bar, copy and paste the formula to each new cell or use the fill handle. The fill handle can help you save time by automatically applying and adjusting the formula to the desired cell range. After setting a cell constant, use the fill handle ("+") at the bottom right corner of the formula cell to drag the formula to a selected cell range. You can use the fill handle to apply your formula to cells either vertically or horizontally.

Please note that none of the companies mentioned in this article are affiliated with Indeed.