Excel for Architects – Using Names to Manage Your Data

Quick question – who’s easier to remember, “Bob” or “A1:D22”? My guess is you’d have an easier time with “Bob”. It’s easy, it’s familiar and it’s short. Now imagine you have a room filled with people named “B2:B33”, “C12:D24” and “E5:G33”. Aside from feeling like you’re in a futuristic sci-fi world, I imagine you’d have a really tough time remembering who’s who.

Working in Excel is a lot like working in that room filled with strangely-named people. It’s hard to remember what cells you’re using with all those letters and numbers. Plus, if you’re working with formulas, you need to keep them updated as your cell ranges change. Talk about a headache. Fortunately there’s a way around this confusion – Names and the Names Manager.

What are Names?

Names allow you to associate a short, easy-to-remember name of your choosing to an object in Excel. The types of things that can be named are:

Cell ranges

Formulas

Constants

Tables

Why Use Names in Excel?

Names make it much easier to keep track of things, especially if you’re using formulas. You can create a name for a range of cells. If that cell range changes, no problem. Just update the range in the Name Manger. You don’t need to update every formula manually. Likewise you can create a name for a formula. If you want to use that formula in another formula, just refer to it by name.

Creating Names

You define names using the “Define Name” tool, accessible from the Formulas ribbon.

Click the icon to open the “New Name” dialog box.

Enter the name you want to use. Names cannot contain spaces or special characters. You must also choose the scope for your name. The name can be valid for the entire workbook or just the current sheet. Add a descriptive comment to the name so you can remember the function of your names more easily. Finally, enter the value for the name. The value can either be a cell range, a constant or a formula.

For a cell range, simply enter the range or click the icon and select the cells on screen. A constant is a fixed value that doesn’t change. Enter a constant by first typing “=” followed by the constant value. For example, if your spreadsheet needs to display both metric and imperial dimensions, you could create a constant for the conversion factor. The named constant would look like this:

To create a named formula, simply type “=” followed by the formula in the “Refers to:” field.

You can also use other names in a named formula. In the example below, the formula “TotalArea” adds together the “FirstFloorArea” and “SecondFloorArea” formulas.

Using Names

Anytime you want to use a name, just type it in where you would otherwise enter a value or cell range. If you want to use a constant in a formula, type “=” to start the formula, then start typing the constant name. Excel will pop-up a list of your available names.

Simply choose from the list to insert the name you want to use.

Updating Your Names

Use the “Name Manager” to update and manage your names. It’s located in the Formulas ribbon.

From the Name Manager dialog, you can see all of the names in the current workbook. You can edit or delete any of the names.

If you rename a name, all of the instances of that name will also change. Likewise, be careful deleting a name. This will delete any formulas using the name.

Conclusion

Names are a great way to manage the complexity of your spreadsheets. They provide an easy shorthand to refer to ranges, values and formulas. One of the biggest benefits to using names is that you can update the value of the name in one location. All instances where the name is used will update as well. This can save you a lot of time and ensure your spreadsheets remain accurate as they change.

For more information on using names in Excel, check out this link on Microsoft’s website. You can find more posts from the Excel for Architects series here.