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.