Sometimes we use a table array formula in **Excel** and after that, we need to edit it. Editing an array formula is different than editing a normal **Excel** formula. This tutorial will teach you **how to edit table array in Excel in 2 suitable ways**. Let’s get started!

## Download Practice Workbook

You can download the** Excel workbook** from here.

## 2 Suitable Ways to Edit Table Array in Excel

We can edit table array formula in **Excel **in **2** suitable ways. One is to **expand the current array formula** and the other is to **change the current formula**. We will discuss both ways in this article.

### 1. Expand Array Formula

Let us introduce our dataset first. We have a dataset of **6 Sales Persons**, their **Number Sold** quantities, and the **Unit Price** of the quantities. We have used an array formula in column **E** to calculate the **Total Sales** of the individual person. But the formula is used for** 4** sales persons. Now we want to expand the formula till the end of the column **Total Sales**.

Follow the steps below to expand the current table array formula.

**Steps:**

- First, select cell
**E5**and it will show the current array formula:

`=C5:C8*D5:D8`

- Second, select all the cells that contain your current array formula and the blank cells next to the empty cells where you want to expand the array formula. In this example, we have selected cells (
**E5:E10**).

- After that, press
**F2**which will allow you to edit the formula.

- Subsequently, type the formula according to your demand which will allow you to expand the formula. We have expanded the formula till the end of the column and hence we have typed the formula below:

`=C5:C10*D5:D10`

- Next, press
**Ctrl+Shift+Enter**and it will expand your array formula as you want.

**Read More: ****How to Expand Table Array in Excel (5 Simple Ways)**

### 2. Change Array formula

If we want to change an array formula, we can also do it in **Excel**. To demonstrate the example, we have taken the same dataset as before where we have **6 Sales Persons**, their **Number Sold** quantities and the **Unit Price** of the quantities. We have used an array formula in cell **C12** to calculate the **Total Sales**. But the formula is used for **4** sales persons. Now we will change the formula and calculate the **Total Sales** for all the sales persons. Follow the steps below to change the current table array formula.

**Steps:**

- Firstly, select cell
**C12**and it will show the current array formula:

`=SUM(C5:C8*D5:D8)`

- Secondly, type the formula according to your demand. We have changed the formula to calculate the
**Total Sales**for all the sales persons:

`=SUM(C5:C10*D5:D10)`

- Finally, press
**Ctrl+Shift+Enter**and it will change your array formula as you want and calculate the**Total Sales**for all the sales persons.

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

## How to Use VLOOKUP Function in Excel Table Array

We can also use **the VLOOKUP function** in **Excel** table array. If we want to compare two columns and find the difference between them, it can be done easily using the **VLOOKUP** function. In order to solve this example, we have taken the same dataset as the following figure where we have **8 Teams** from **Group A** and **4 Qualified teams** from **Group A**. Now we will compare these two columns using the **VLOOKUP** function and display the **Not Qualified teams** from **Group A**.

Follow the steps below in order to compare two columns and find missing values using the **VLOOKUP** function.

**Steps:**

- To begin with, select cell
**D5**and type the following formula:

`=FILTER(B5:B12,ISNA(VLOOKUP(B5:B12,C5:C8,1,FALSE)))`

- Lastly, press
**Enter**and since it is an array formula, you will see all the**Not Qualified teams**from**Group A**under the column**Not Qualified**like the image below.

**🔎**** How Does the Formula Work?**

**VLOOKUP(B5:B12,C5:C8,1,FALSE):**Firstly, this part of the formula looks for (**B5:B12**) in the range (**C5:C8**).**ISNA(VLOOKUP(B5:B12,C5:C8,1,FALSE)):**Secondly,**the****ISNA****function**finds all the**False**statements.**FILTER(B5:B12,ISNA(VLOOKUP(B5:B12,C5:C8,1,FALSE))):**Finally,**the FILTER function**function sorts all the**FALSE**statements and give the final output.

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

## Things to Remember

- If you have a subscription to
**Microsoft Office 365**, you have to press**Enter**instead of**Ctrl+Shift+Enter**. - The
**FILTER function**is only available in**Microsoft Office 365**. So if you have an older version of**Microsoft Office**, you won’t be able to use this function.

## Conclusion

Hence, follow the above-described steps. Thus, you can easily learn **how to edit table array in Excel**. Hope this will be helpful. Follow the **ExcelDemy** website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.