• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Constants in Excel

January 26, 2022 by Andrew Childress
How to use constants in Excel

When you work in Excel, it’s useful to copy formulas by dragging them. These are called constants. Here's how to use constants in Excel.

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!

Constants Excel

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.

Constants in Excel

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.

How to use constants in Excel

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.

Category: Excel TutorialsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Choose ExcelCHOOSE in Excel
Next Post:COLUMN in ExcelColumn function in Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top