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:

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.

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.