• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Course
  • Free Training
  • Resources
  • Login

Using Cell Names in Excel

As previously mentioned, one of the biggest complaints that engineers have against Excel is that it’s difficult to understand formulas in a spreadsheet. Formulas refer to cell references like C6, D7, etc. However, Excel has a feature called Named Ranges that allows you to assign names to cells.

Spreadsheet 03x contains a calculation for the change in internal energy for a constant volume with changing pressure. The formula used is:

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

The Excel formula for this would be =C5(C7-C6)/(C8-1).

This isn’t the most readable formula. If the formula above wasn’t provided on the worksheet, it would be difficult to understand. We can assign names to the cells we’re referencing to make this formula easier to read.

To assign a variable to cell C5, click on the cell. Above the spreadsheet, to the left of the formula bar, is the “name box:”

Click inside the name box and type a name. In this case, use the letter V for volume, then press Enter.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

We can do this for the remaining variables. However, Excel doesn’t accept certain names. For the initial pressure, the name P1 refers to a cell elsewhere in the worksheet. If you type this into the name box, Excel simply takes you that cell. Instead, use an abbreviation like pres1 for initial pressure and pres2 for final pressure. The letter k can be used for the constant k.

Now that we’ve renamed the variables, we can use those variables in a formula. Edit the inputs for the calculation to use the variables as references. When you use one of these variables, it will appear in a dropdown list with a small tag icon (). This icon lets you know it’s a named cell rather than a function.

Enter the entire formula using the variables:

    =V*(pres2-pres1)/(k-1)

This formula will be much easier for someone else to understand when they read your spreadsheet, especially if they’re unfamiliar with the calculation.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Primary Sidebar

Featured Posts

Calculate a Derivative in Excel from Tables of Data

Using Excel’s Equation Editor to Document Equations

Intro to Excel VBA User Defined Functions

Combine UDF’s and Named Cells to Boost Efficiency in Excel

Calculating the Integral of an Equation in Excel with VBA

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in