Using range names in Excel

Using range names in Excel allows you to quickly navigate to areas of your worksheet and makes formulas much easier to create. A range name is simply a name you assign to a range of data and is much easier to remember than a cell address.

How to name a range

Suppose you have the following data, and you would like to name cells B3 through D3 as Education.

To name a range, select the cells you would like to name. In this example, select cells B3:D3. Click the Name box to the left of the Formula bar and type “Education.” Hit Enter to create the name.

Note that range names cannot have spaces.

Working with range names

You can now use the range name in a formula. In the formula, you will substitute the name of the range for the cells you would typically reference. Let’s sum the Education range as an example. Click in cell E3 and type the formula =SUM(Education) and hit Enter.

The formula returned the sum of the range name named Education (B3:D3).

Viewing range names

If you have forgotten what range names you have used, you can view all the range names that are in your workbook. To do this, click the drop-down arrow in the Name box to see the named ranges in the workbook.

— Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent, Ohio; Jennifer Cainas, CPA, DBA, is a clinical professor at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. Their website, Accounting is Analytics, has resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact Courtney Vien at Courtney.Vien@aicpa-cima.com.