Excel Tips and Tricks Tip 3 (Quickly Create Many Range Names)

Before reading this article, please go through the following articles:

1. Excel Tips and Tricks

2. Excel Tips and Tricks 2

With this tip, you'll learn how to create many range names quickly. The scenario is that you have a table and you want to create Name Ranges for each column for making calculation steps easier.

Suppose you have a table like one in Picture 1 below:



In the picture above we have a table with multiple headers and the goal is to create name ranges for every column of data inside the table. Doing this one by one may be simple and might feel not so time-consuming but just think, if the size of the table (the number of columns) is big then it surely would be a heck. But no worries, Excel has the power to cope with this. You can easily create name ranges for each of the column with the header name, no matter how many columns a table has. Do check before trying this trick that your table has header names specified.

Procedure

STEP 1

Select the table (Data with Headers).

Step 2

For Excel 2003 Users: go through "Insert" => "Name" => "Create". In the "Create Names" dialog box, choose "Top row" and click "OK". (Refer to Picture 2 below.)



Picture 2

For Excel 2007/2010/2013 users go to the "Formulas" tab. Under the "Define Names" sub-tab you'll find "Create from Selection", click it. In the "Create Names from Selection" dialog box, select "Top row" and click "OK". (Refer to Pictures 3 & 4 below.)



Picture 3



Picture 4

Voila! That's all. For this example you've successfully created five Range Names in one go. You will see them all in "Name Manager" (see Picture 5).



Picture 5

Note: The point that I must mention is, your column header name shouldn't have any blank spaces or special characters since that's not valid as per the naming convention of Range Names. If there's any space or special character then Excel will replace it with an "underline" character ("_"). Please have a look at Picture 5 and see, I have a column named "Previous Month". In the Name Manager the name defined for this column is "Previous_Month". You will see that the blank space is replaced with an underline character.

Now you can use these names for other manipulations. In Picture 6 below you will see that cell "F19" has the Range Name "Previous_Month" to calculate the gross.



Picture 6

A sample sheet is attached with this article, please see it for any clarification. I hope you enjoy learning this trick. Follow for more!!