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!
How to Edit Table Array in Excel: 2 Suitable Ways
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
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.
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.
Download Practice Workbook
You can download the Excel workbook from here.
Conclusion
Hence, follow the above-described steps. Thus, you can easily learn how to edit table array in Excel. Hope this will be helpful. Don’t forget to drop your comments, suggestions, or queries in the comment section below.
