How to Name a Table Array in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

This tutorial will demonstrate how to name a table array in excel. It is a very important argument in the LOOKUP Function. In case, you want to find a certain column or group of columns in the worksheet, then naming the table array is very useful. After naming a table array, you can find it with selected cells under it only by searching by the proper name. So, it shortens our work and is essential for us to learn using it quickly.


Download Practice Workbook

You can download the practice workbook from here.


What Is a Table Array?

Table Array is one of the most important arguments of any LOOKUP function such as the VLOOKUP function or the HLOOKUP function. Table array is mainly used to find specific data or formulas or groups of formulas or data. If you use this function, it will search the particular data you have inserted. So, giving the name of the table array can be helpful for simplifying the work.


Step-by-Step Procedures to Name a Table Array in Excel

If you follow the steps correctly, you should learn how to name a table array in excel on your own. The steps are:

Step 1: Creating Table Array

At the very beginning, we will create a table array using the VLOOKUP Function. The VLOOKUP Function is mainly used when you have to find a table or some info or any data by row. The process of this step is described below.

  • First, arrange the proper dataset. In this case, we have the Salesperson in Column B and two years 2021 and 2022 in Column C and D.

Procedures to Name a Table Array

  • Next, in cell D14 insert the following formula.
=VLOOKUP(B14,B5:D11,2,0)

Procedures to Name a Table Array

  • After that, you will get the desired result.

Procedures to Name a Table Array

Read More: What Is a Table Array in VLOOKUP? (Explained with Examples)


Step 2: Naming Table Array

After creating the table array, now our target is to name it properly. For naming the table array, we will follow the below process.

  • At first, select the desired column( in this case Column B).
  • Then go to Formulas > Defined Names options.

Procedures to Name a Table Array

  • Second, the New Name dialog box will come on the screen.
  • Next, give a suitable name and select the cells you want to use, and press OK.

Procedures to Name a Table Array

  • Last, if you write the name, you will get the data like the below image.

Procedures to Name a Table Array

Read More: How to Find Table Array in Excel (4 Suitable Examples)


Similar Readings


Step 3: Creating Dynamic Named Range

Dynamic ranges are also known as expanding ranges. Dynamic ranges normally expand automatically and connect with the most suitable data. In this case, we will use the OFFSET function to create the dynamic range. The main thing about the OFFSET function is, that it can recalculate all data or formulas after making any change in the worksheet. The steps are.

  • To begin with, go to select range > Formulas > Defined Names.

Procedures to Name a Table Array in Excel

  • In addition, in the New Name dialog box, insert the following formula and press OK.
=OFFSET(C5,0,0,COUNTA(C:D),1)

Procedures to Name a Table Array in Excel

  • Furthermore, if you write the name in any cell, you will find the data under this data like in the previous step.

🔎 How Does the Formula Work?

  • COUNTA(C:D): It represents the selected columns of the worksheet.
  • OFFSET(C5,0,0, COUNTA(C: D),1): In this portion, the first value is the first selected cell, the COUNTA function shows the selected columns and the last value presents the cell’s necessary width.

Read More: How to Convert Range to Array in Excel VBA (3 Ways)


Step 4: Editing Named Ranges

When dealing with a certain project, it is very obvious that you need to edit or delete or add new data in the named ranges. To do so, we have to follow the below steps.

  • Firstly, go to select range > Formulas > Defined Names>New Manager options.

Procedures to Name a Table Array in Excel

  • Secondly, the New Manager dialogue box will open up on the screen. You can use New or Edit or Delete according to your need.

  • Thirdly, if you want to edit then click the Edit option and the Edit Name option will come on your screen. You can make any change in this Name or Refers to options to get the desired result.

  • Lastly, if you want to delete something use the Delete option and choose OK like the below image.

Read More: How to Edit Table Array in Excel (2 Suitable Ways)


How to Name an Array Constant

Array constants are a set of values that normally come with curly brackets {}. Now, we want to name an array constant in the following order.

Steps:

  • At first, go to select range > Formulas > Defined Names.
  • Second, the New Name dialog box will come on the screen. Name it as you want and select the desired cells and press OK.

  • Last, you will get the desired result.


Things to Remember

  • The main focus is to create the table array at first. So, the first step should be done carefully.
  • In the case of naming, if there remains any space between words then you have to use “_” in between the words. Otherwise, it won’t work.
  • Only Edit or Delete or New options, if it is very necessary.
  • In the case of Naming an array constant, the constants must be kept in curly brackets {}.

Conclusion

Henceforth, follow the above-described methods. Thus, you will be able to name a table array in excel. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo