Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.

Suitable Ways to Edit Table Array in Excel

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

Suitable Ways to Edit Table Array in Excel

  • 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).

Suitable Ways to Edit Table Array in Excel

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

Suitable Ways to Edit Table Array in Excel

  • 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)

Suitable Ways to Edit Table Array in Excel

  • 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)

Suitable Ways to Edit Table Array in Excel

  • 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.

Suitable Ways to Edit Table Array in Excel

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)))

Suitable Ways to Edit Table Array in Excel

  • 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.


Related Articles

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo