When you work in Excel, it’s useful to copy formulas by dragging them. This updates values automatically, allowing you to quickly process large volumes of data. But sometimes, you don’t want certain values updated. These are called constants, and they’re easy to work with in Excel. Let’s learn how to use constants in Microsoft Excel.
How to Use Constants in Excel
Imagine a simple data layout like the one below. You have values in cells A1, A2, A3, and A4. You want to divide each of these values by the contents of cell E1. Copying the formula downward will work here, but not without applying a constant.
To demonstrate, click into cell B1 and enter your first formula:
=A1/E1
Hit Enter on your keyboard, and Excel will return your solution. Now, drag the formula downward across cells B2:B4. Notice that the three new formulas all error out:
#DIV/0!
This is because Excel updated the formulas to A2/E2, A3/E3, and A4/E4. But cells E2, E3, and E4 are blank.
To correct this, remove the three new formulas and return to the formula in cell B1. With your cursor positioned after E1 in the formula bar, hit F4 on your keyboard.
This locks in cell E1 as a constant value. Now, when you drag the formulas, cell E1 will remain constant, and the math operations will perform correctly.
If you can’t use F4, simply place two dollar signs in your formula, making it read:
=A1/$E$1
The dollar signs lock in E as a constant column and cell 1 within column E as a second constant.
As you can see, using constants in Excel is the perfect way to ensure that formulas work for you. It enables you to copy formulas across multiple rows and columns, while updating only the reference values that you’ve chosen.
CHOOSE in Excel