Ever feel lost scrolling through multiple Excel sheets, trying to find that one cell with the data you need for your formula? Maybe you needed a calculation from another sheet but couldn’t remember if the data was in B6, B10, or somewhere else. Trust me, we’ve all been there. Luckily, Excel offers a way to make your life easier—defining cell names and using them in functions and formulas. With this feature, you can assign a name to individual cells or ranges, letting you reference them by name instead of their location. Let’s get started!

Why Define Cell Names?

In Excel, formulas can quickly become confusing when you’re referencing cells across different sheets. It’s especially frustrating when you have to go back and forth between tabs to find the exact cell you need—whether it’s for a simple formula or a complex function.

Creating cell names Excel will make the formula or function creation process smoother and more understandable. Instead of hunting down cell coordinates, you can use recognizable names.

Here’s how to define cell names and use them in your formulas and functions in Excel.

Defining a Single Cell Name

Let’s start simple by defining a name for a single cell. Say you have a discount rate that you want to apply to a column. Normally, you’d write a formula or function in Excel like this:

= D6 * B16

But every time you drag this formula down the column, Excel’s default behavior is to use relative cell references. This means the reference to the discount rate may shift as you move down, and suddenly you’ll be calculating using the wrong discount.

RELATED TOPIC: How to Use the 3D Reference in Excel

The fix: Absolute Cell References

  • Go to your cell with the discount rate (e.g., B16).
  • In your formula, place a dollar sign ($) in front of both the column letter and row number: = D6 * $B$16.
  • Now drag the formula down to other rows, and it will only pull the discount from B16.

But why stop at absolute references? We can give B16 a name like “DiscountRate.” That way, when you write your formula, instead of reading = D6 * $B$16, you can write = D6 * DiscountRate. It’s much easier to read and manage.

Steps to Define a Single Cell Name

  1. Click on the cell you want to name, such as your discount rate in B16.
  2. Look up to the top-left of your screen, where you’ll see the Name Box (just to the left of the formula bar).
  3. Type in a new, meaningful name—let’s call it DiscountRate.
  4. Press Enter.

Boom! Now B16 is officially a named cell DiscountRate, and you can use this name in any formula and function across your entire Excel workbook.

RELATED TOPIC: How to Concatenate or Combine Data in Excel

Naming a Range of Cells in Excel and Using them in a Function or Formula

Let’s say you also want to sum up quantities of items across multiple rows. Rather than worrying about where the numbers are on your sheet, you can assign a name to that range of cells and then use them in functions and formulas in you Excel workbook.

Here’s how:

Steps to Define a Range of Cells

  1. Highlight the range you want to define, for example from B6 to B50.
  2. Navigate to the Name Box again, and give it a name—like ProductQuantity. Remember, no spaces are allowed, but underscores (_) work great.
  3. Hit Enter.

The next time you want to sum up your product quantities, you can skip over references like =SUM(B6:B50) and just type =SUM(ProductQuantity).

The Power of Using Named Ranges

By naming this range of cells in Excel, you can perform calculations like SUM or other formulas and functions. No need to go and find the specific cells. Even better, if you move to another Excel worksheet within the same workbook and still need this data, you’ve got access.

  • Go to another sheet.
  • In the formula bar, type ProductQuantity instead of looking for the data’s cell references.
  • Excel will automatically bring the values from the named range into your current sheet.

Using Named Cells Across Worksheets

One of the real timesavers is using named cells or ranges when juggling multiple worksheets. Imagine you’re working with a workbook that has 20 different tabs—it’s almost impossible to keep track of where everything is.

With defined names like DiscountRate or ProductQuantity, moving between sheets no longer means flipping through tabs and searching for cells. You just need to type the name, and Excel will give you the right values, no matter which sheet you’re on.

RELATED TOPIC: Mastering Excel: Absolute and Relative Cell References Made Easy

Example: Using Named Cells Across Sheets

Let’s say you’re doing calculations on Sheet1, but you need to reference ProductQuantity from Sheet2:

  • Go to the formula bar.
  • Click Formulas at the top.
  • Click Use in Formula, and select ProductQuantity from the dropdown list.

This allows you to work smoothly across different sheets while maintaining accurate data references.

Removing or Modifying Named Cells

Maybe at some point, you’ll want to delete or change the names of your cells or ranges. No problem!

Steps to Delete a Name:

  1. From the top ribbon, go to the Formulas tab.
  2. Click on Name Manager.
  3. Select the name you want to get rid of and click Delete.
  4. Confirm the action.

Beware: After Deletion

Once you delete a named cell or range, Excel won’t know what to do with formulas or functions that rely on those names. You’ll see #NAME? errors in place of your values. At this point, you either redefine the names or update the formulas manually.

RELATED TOPIC: How to use the SUMIF and SUMIFS Functions in Excel

Conclusion

Defining and using cell names in functions or formulas in Excel can save you loads of time, especially if you’re managing large workbooks with multiple sheets. By naming cells or ranges, you’re making your formulas easier to read and far more flexible. Plus, navigating different sheets becomes a breeze when you no longer have to remember where everything is.

This approach may take a little setup, but I promise, it’s worth it long-term. Give it a try in your next Excel project and let me know what you think! If you have any questions or feedback about cell naming, feel free to drop them in the comments.

Until next time, happy Excel-ing!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR