Excel Tip #1: Avoid Mistakes with User-Friendly Cells

Updated: July 10, 2020

We imagine a lot of you are in front of your computers these days, so we're reprising this popular series from a few years ago. If you can stop your dog from blocking your view of your spreadsheet, in addition to this series, you may be interested in our upcoming virtual combo course on spreadsheet problem solving and VBA programming.

Share your tips, on Engage!

Do you have some tips of your own about spreadsheets? If you're a member, share them on AIChE Engage.

Spreadsheets have been a useful and trusted tool for chemical engineers ever since they became popular back in the 1980s. While there are certainly more specialized tools, Microsoft’s ubiquitous Excel spreadsheet can handle many tasks for a chemical engineer. And when a more sophisticated tool is called for, Excel will often have served as a stepping stone to a more specialized application.

Because Excel is ubiquitous, proper training is often overlooked, leaving many chemical engineers to learn Excel on the fly, whether as students or on the job. The lack of formal training sometimes means overlooked shortcuts and and techniques that might otherwise help chemical engineers save time.

In this series of posts, we will look at some great ways to make Excel even more useful, so look for more posts in coming weeks. (Note that these tips are based on Excel 2013, so it's possible you may see some slight variations in other versions.) By the way, before we launch into this post's tip, if you've not already seen it, there is an earlier series on Excel tips that you may find useful. You'll notice that the very popular original series featured cats at the keyboard, so we decided to use dogs as the mascot for this series, in case you were wondering about the picture above. And now, on to the Excel tip below.

Use names instead of cell addresses

Consider the ideal gas law calculation in the Excel spreadsheet below. For easy readability, this ideal gas law calculation has labels in the left column, values in the center column, and units in the right column. 

Contrast the following formulas for calculating the value in cell C6:

Although this is a simple example, the advantage of the formula on the right is evident. In order to reverse-­engineer formulas that use cell addresses, such as the one on the left, you would have to trace back the source of each quantity. The formula on the right uses cell names that relate to the variable names from the familiar algebraic ideal gas equation. The style of the spreadsheet layout also improves read­ability. In Figure 1, the labels in column B are the same as the names on the cells in column C.

There are three common ways to create names for cells. A convenient method is to select the cell, and type the name into the Name Box field above the column A label:

You can also transfer the label from an adjacent cell onto the cell of interest using Create from Selection in the Defined Names group on the Formula tab of the Ribbon, as shown below. In fact, more than one label can be transferred with a single command, as shown here:

Use the Name Manager in the same Defined Names group to create, edit, and delete names. Cell names generally have global scope in the workbook, but it is possible, using the Name Manager, to create names that have scope only in the worksheet where they are created.

Note that certain names are not allowed. First, you cannot create a name that is the same as a cell address. Given the size of the modern worksheet (the Excel spreadsheet has 2¹⁴ = 16,384 columns and 2²⁰ = 1,048,576 rows — a total of 2³⁴ cells), with columns out to XFD, it is easy to confuse a name with a cell address. Second, you cannot use the letters R or C as names or those letters followed by any digits. This restriction harkens back to the R-C method of cell addressing (i.e., row-column), which is rarely used today. Finally, if you transfer labels as names, you will find that in some cases Excel takes editorial license and changes them; for example, the label A(0) becomes the name A_0. For this reason, it is always good practice to check the transferred names before you proceed.

Names can also be defined for rectangular ranges of cells. These names can then be used conveniently in formulas that reference those ranges, such as statistical functions and array formulas.

Names can also be used in VBA code. It is bad practice to use cell addresses in VBA Subs and Functions, because these references will not update in the event any changes are made on the spreadsheet that move the content of these cells.

So, the proverbial bottom line is to use names wherever possible.

Three related tips

It is good practice to create a nomenclature table for names, possibly on a separate worksheet. This can be started conveniently using the Paste List command on the Paste Names dialog window. You can access the Paste Names dialog window via the Defined Names group and Use in Formula drop-down list. Your nomenclature list will not be updated automatically, so it is best to generate it as part of the final documentation of a spreadsheet.

When should names not be used? The advantage of cell addresses, both relative and mixed-reference, is in copying formulas and patterns, like index columns, into other cells, for example, using the Autofill feature of Excel, initiated by double-clicking the Fill Handle in the lower right-hand corner of a selected cell. In such cases, the use of cell addresses should be preserved.

If you create formulas that involve cell addresses and later create names for those cells, the names in the formulas will not be automatically updated. You can update the formulas with cell names via the Apply Names dialog window accessed from the drop-down list under Define Name in the Defined Names group (on the Formula tab of the Ribbon).

The use of cell names is especially valuable on larger spreadsheets and even smaller ones that will be used, and hopefully understood, by others.

More tips and techniques

In this series, we will present more tips covering a variety of areas to  help you use Excel smarter and with better outcomes. In the meantime, if you're looking for more than a few useful tips and need a full crash course, you should check out the AIChE Academy's "Spreadsheet Problem-Solving and VBA Programming Combo Course - Virtual," where these tips come from, and also check out the other Excel courses available through the AIChE Academy at www.aiche.org/academy.

Want more Excel tips for chemical engineers?

If you know you want to delve even deeper than this blog series – or if our Excel tips leave you hungry for more – be sure to check out AIChE’s virtual combo course on spreadsheet problem solving and VBA programming. It’s taught by David E. Clough, the author of this series, and combines two of AIChE’s most popular spreadsheet courses, Spreadsheet Problem-Solving for Chemical Engineers and Excel VBA Programming for Chemical Engineers.  

Share your tips, on Engage!

Do you have some tips of your own about spreadsheets? If you're a member, share them on AIChE Engage.

This series is drawn from an article by David Clough that appeared in AIChE's CEP Magazine. You can find the current issue and an extensive archives of back issues at www.aiche.org/cep.