#239 Excel Range Names

Using range names makes Excel formulas much easier for you to work with and for others to understand. To illustrate, which of the following would you rather deal with?

Option A =SUM(E2:E10)

Option B =SUM(Hours)

I’m pretty sure that most of you chose option B, right? The range name tells you in plain English what the formula is doing. The bigger and more complex your spreadsheets, the more helpful range names can be.

The process is pretty simple. Once you name a range of cells you can easily use them in any formula without having to jump all over the place looking for the starting and ending range cell references.

Creating a Range Name

1. Select the cell or range of cells you’d like to name.

2. Click the Name box located at the left end of the formula bar, type the name that you want to use to refer to your selection then press the Enter key.
(Range names can be up to 255 characters)

Auto-Magically Naming Ranges

If your data already has column and/or row headings, you can use those as your range names and automatically create all your ranges at once instead of one-by-one. Using the example below, let’s see how this works.

1. Select the range that you want to name, including the row or column labels.
(A1:E10 in our example)

2. Use the CTRL + SHIFT + F3 shortcut on your keyboard.

3. Designate where the labels for the range names are. (Top row, Left column, Bottom row, or Right column) and then click OK

NOTE: You won’t notice anything different at this point. If you don’t trust me, pressing F5 will display a list of all the ranges that you just created.

Using Names in Formulas

Now that you have your range names, formulas are super quick and easy.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s