If you want to expand a table array in Excel, then this article will be helpful for you. The main objective of this article is to explain how to **expand table array** in Excel.

**Table of Contents**hide

## Download Practice Workbook

## 5 Simple Ways to Expand Table Array in Excel

Here, I have taken the following dataset to explain this article. This table contains **Student Name **and their obtained marks in **Physics**, **Chemistry**, and **Math**. I will use this dataset to explain how to **expand table array **in Excel.

### 1. Manually Expanding Table Array by Typing

In this first method, I will explain how to **expand table array **in Excel **manually **by **typing**. Let’s see the steps.

**Steps:**

To begin with, I will add a new **column **to my **table array**.

- Firstly, select the cell where you want your
**column header**. Here, I selected cell**F4**. - Secondly, in cell
**F4**write the name you want for your**column header**. Here, I wrote**English**.

- Thirdly, press
**ENTER**and you will get your**new column**.

Now, I will add a **new row **to my **table array**.

- Firstly, select the cell from where you want to start the
**new row**. Here, I selected cell**B10**. - Secondly, in cell
**B10**type the value you want to show. Here, I wrote**Brad**because I want to insert obtained marks for a student named**Brad**.

- Thirdly, press
**ENTER**and you will get your**row**.

Here, you can see that I have gotten a **new row** for the student named **Brad**.

Now, I have inserted **2** more **rows** in the same way to my** table array**.

Finally, I entered the obtained numbers for every student in each subject and got the **expanded table array** I wanted.

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

### 2. Using Insert Command to Expand Table Array in Excel

Here, I will explain how to** expand table array** in Excel using the **Insert **command. Let’s see the steps.

**Steps:**

- Firstly, select any cell from the
**last column**of your**table array**. Here, I selected cell**E5**.

- Secondly, go to the
**Home**tab from the**Ribbon**. - Thirdly, select
**Insert**. - After that, select
**Insert Table Columns to the Right**.

Now, you will see a **new column **is added to your **table array**.

- Next, change the column name as you want. Here, I wrote
**History**as the**column header**.

Here, I will add **new rows** to my **table array**.

- Firstly, select a cell from the last
**row**of your**table array**. Here, I selected cell**B9**. - Secondly, go to the
**Home**tab. - Thirdly, select
**Insert**. - After that, select
**Insert Table Row Below**.

Now, you will see a **new row **is added to your **table array**.

Here, in the same way, I added **2** more **rows**.

Finally, I have entered the obtained numbers for the added **rows** and **columns** and got my desired **expanded table array**.

**Read More: ****How to Create a Table Array in Excel (3 Methods)**

### 3. Applying Resize Table Feature to Expand Table Array in Excel

In this method, I will explain how you can apply the **Resize Table** feature to **expand table array** in Excel. Let’s see the steps.

**Steps:**

- Firstly, select any cell from your
**table array**. - Secondly, go to the
**Table Design**tab. - Thirdly, select
**Resize Table**.

Now, you will see a** dialog box **named **Resize Table** has appeared.

- Firstly, select the new table range you want for your expanded table array for
**Select the new data range for your table**. - Secondly, select
**OK**.

Here, you will see that I have got the **expanded table array** I wanted.

Finally, I entered data in the** column** and **rows **I added.

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

### 4. Expanding VLOOKUP Table Array in Excel

In this method, I will explain how to **expand** the **VLOOKUP table array **in Excel. Here, I will show how you can expand the** table_array** in **the VLOOKUP function**. In the following image, you can see that I have a dataset. I will find the obtained marks in **Math** for the student named **Emma**.

Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to find the marks in
**Math**. Here, I selected cell**C12**. - Secondly, in cell
**C12**write the following formula.

`=VLOOKUP(C11,B4:E9,4,FALSE)`

Here, in the **VLOOKUP** function, I selected cell **C11** as **lookup_value**, **B4:E9** as **table_array**, **4** as **col_index_num**, and **FALSE **as **range_lookup **because I want an exact match here. The function will return the exact match for the **lookup_value**.

- Thirdly, press
**ENTER**to get the result.

Here, I have added **3 **more rows to my dataset.

Now, you can see the formula is not working after entering a name from the newly added rows. The reason here is that the **table_array** remains the same.

Now, I will show you how you can **expand the table array** in a dynamic way.

- Firstly, select the data range you want as your
**table array**. - Secondly, go to the
**Insert**tab. - Thirdly, select
**Table**.

Now, a **dialog box** named **Create Table **will appear. The selected range will be inserted automatically here as data for the table.

- After that, select
**OK**.

Here, you can see I have inserted a table.

- Now, select the cell where you want to find the marks in
**Math**. Here, I selected cell**C12**. - Next, in cell
**C12**write the following formula.

`=VLOOKUP(C11,Table17[#All],4,FALSE)`

Now, in the** VLOOKUP **function, I selected cell **C11 **as **lookup_value**, **Table17[#All] **as **table_array**, **4 **as **col_index_num**, and **FALSE **as **range_lookup **because I want an exact match here. The function will return the exact match for the **lookup_value**.

- After that, press
**ENTER**.

Here, I have added **3** new **rows** to the **table array**.

Now, you can see that the formula works for the values from newly added **rows** because the table array here is dynamic and can be expanded. This is how you can **expand table array **for **VLOOKUP**.

**Read More: ****How to Use VLOOKUP Table Array Based on Cell Value in Excel**

### 5. Using Dynamic Named Range to Expand VLOOKUP Table Array

In this method, I will show you how you can expand the **VLOOKUP** **table array **in Excel by using **Dynamic Named Range**. In the following image, you can see my **table array**. I want to find the obtained marks in **Chemistry **for **Sam **using the **VLOOKUP **function.

Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want the obtained marks in
**Chemistry**. Here, I selected cell**G5**. - Secondly, in cell
**G5**write the following formula.

`=VLOOKUP(G4,B4:D9,3,FALSE)`

Here, in the **VLOOKUP** function, I selected cell **G4** as **lookup_value**, **B4:D9** as **table_array**, **3** as **col_index_num**, and **FALSE **as **range_lookup **because I want an exact match here. The function will return the exact match for the **lookup_value**.

- Thirdly, press
**ENTER**to get the result.

You can see the formula is working.

After that, I added **3** more **rows **to expand the **table array**.

Now, I tried to find obtained marks for a student from the newly added **row**. You can see that the formula is not working because the **table array** did not expand as I expanded my dataset.

Here, I will show you how you can make the **table array dynamic** here. So that, it expands whenever new data is entered.

- Firstly, go to the
**Formulas**tab. - Secondly, select
**Define Name**.

Now, a** dialog box will **appear.

- Firstly, define the
**Name**. Here, I wrote**Dynamic_Range**as**Name**. - Secondly, write the following formula as
**Refers to**.

`='Using Dynamic Named Range'!$B:$B:INDEX('Using Dynamic Named Range'!$D:$D,COUNTA('Using Dynamic Named Range'!$D:$D)) `

**Formula Breakdown**

**COUNTA(‘Using Dynamic Named Range’!$D:$D)****—->**Here,**the COUNTA function**will return the number of**rows**that contains any text or number.**Output: 9**

**INDEX(‘Using Dynamic Named Range’!$D:$D,COUNTA(‘Using Dynamic Named Range’!$D:$D)) —->**turns into**INDEX(‘Using Dynamic Named Range’!$D:$D,9) —->**Here,**the INDEX function**will return the value from within the table.**Output: $D$9**

**‘Using Dynamic Named Range’!$B:$B:INDEX(‘Using Dynamic Named Range’!$D:$D,9) —->**turns into**‘Using Dynamic Named Range’!$B:$B:$D$9 —->**Here, the formula will return a dynamic**table array**range.**Output: $B:$B:$D$9**

- Thirdly, press
**OK**.

- Now, select the cell where you want the obtained marks in
**Chemistry**. Here, I selected cell**G5**. - Next, in cell
**G5**write the following formula.

`=VLOOKUP(G4,Dynamic_Range,3,FALSE)`

Here, in the **VLOOKUP** function, I selected cell **G4** as **lookup_value**, **Dynamic_Range** as **table_array**, **3** as **col_index_num**, and **FALSE **as **range_lookup **because I want an exact match here. The function will return the exact match for the **lookup_value**.

- Finally, press
**ENTER**.

Now, I have added **3** more **rows **to **expand** the **table array**.

Here, you can see that I used the same formula to find the marks in** Chemistry** for a student from the newly added rows. And the formula is working. That means I have successfully **expanded **my **table array** for **VLOOKUP**.

**Read More: ****How to Name a Table Array in Excel (With Easy Steps)**

## Practice Section

Here, I have provided a practice sheet for you to practice how to **expand table array** in Excel.

## Conclusion

To conclude, I tried to cover how to **expand table array** in Excel. Here, I explained** 5** easy and effective ways of doing it. I hope this article was helpful for you. Lastly, if you have any questions let me know in the comment section below.