About This Article
This article was generated using an AI-assisted automation workflow. It reviews Microsoft's official specifications for TAKE, DROP, and CHOOSECOLS, and organizes them so you can safely observe the differences between "taking," "dropping," and "choosing columns" using the same small table.Verification Status: 📘 Confirmed with Microsoft official specifications; Excel physical hardware/app verification not performed
Information Verification Date: October 5, 2026
When you want to extract specific portions from an Excel table, such as "only the first 2 rows," "excluding the last row," or "only the 1st and 3rd columns," the function that allows you to output the results to another location without deleting the original data is TAKE、DROP、CHOOSECOLS.
These three functions are similar, but they serve different purposes.
| Function | Arguments Required | Best Suited For |
|---|---|---|
| TAKE | How many rows and columns to take | First N items, last N items |
| DROP | How many rows and columns to drop | Excluding headers, excluding the end |
| CHOOSECOLS | Which columns to keep | Extract only the required columns |
- Test all three on the same table
- DROP means "exclude" rather than "keep"
- CHOOSECOLS selects by column number.
- Change just one place
- What happens if you specify 0?
- Which one should you use in practical scenarios?
- Keep the spill destination clear
- Best practices for professional use
- GitHub Sample
- Official Documentation
Test all three on the same table
Enter the following dummy data into A1:C4.
| Name | Department | Sales |
|---|---|---|
| Aoki | Sales | 120 |
| Ito | General Affairs | 80 |
| Sato | Sales | 150 |
First, enter the following in another empty cell.
=TAKE(A1:C4,2)
The first two rows are returned as a new array. The original table is not deleted.
Next, change it to a negative number.
=TAKE(A1:C4,-2)
According to the official Microsoft specification, specifying a negative number for the row or column count in TAKE retrieves elements from the end of the array.
DROP means "exclude" rather than "keep"
To return only the data portion excluding the header row, do the following.
=DROP(A1:C4,1)
Exclude the first row.
Use a negative number to exclude the last row.
=DROP(A1:C4,-1)
Placing TAKE and DROP side by side reveals the difference.
flowchart LR
A["元の配列"] --> B["TAKE: 指定分を取る"]
A --> C["DROP: 指定分を除く"]
A --> D["CHOOSECOLS: 指定列を選ぶ"]
B --> E["新しい配列"]
C --> E
D --> E
CHOOSECOLS selects by column number.
This is for when you only want the name and sales, and want to exclude the department column.
=CHOOSECOLS(A1:C4,1,3)
The first and third columns are returned as a new array.
The difference from DROP is that you specify which columns to return rather than how many columns to delete.
Change just one place
=TAKE(A1:C4,2) of 2 -2 change to .
What we see here is that the reference position changes between positive and negative numbers.
Similarly, with DROP,
=DROP(A1:C4,1) =DROP(A1:C4,-1)
Comparing these allows you to verify the difference between the starting and ending sides.
What happens if you specify 0?
Microsoft Support explains that if you specify 0 for the number of rows or columns in TAKE or DROP and it results in an empty array, #CALC! is returned.
Intentionally try the following.
=TAKE(A1:C4,0)
Since the results on an actual Excel device have not been verified, the displayed results are not treated as "measured." Try it in an actual workbook and check how it appears in your currently used Excel.
Microsoft states that CHOOSECOLS results in #VALUE! when the absolute value of the column number is 0 or exceeds the number of columns in the array.
Which one should you use in practical scenarios?
I only want to check the first 10 items
=TAKE(A2:F100,10)
This can be used when checking only the beginning of a large dataset.
I want to remove the header after importing a CSV
=DROP(A1:F100,1)
This is based on the concept of "everything except the first row".
I want to extract only the necessary columns from a report
=CHOOSECOLS(A1:F100,1,3,6)
You can specify the column order to create a separate table. Since it does not directly modify the source table, it is also suitable for creating verification views.
Keep the spill destination clear
These are dynamic array functions. Because the results spill across multiple cells, they cannot spill properly if there is existing data in the output range.
Before assuming the formula is incorrect, check whether any values remain in the range where the results are to be expanded.
Best practices for professional use
A major advantage is that you can extract only the required parts in a separate area without directly deleting or sorting the source data.
For example,
Keep the original CSV intact
Use TAKE to check initial samples
Use DROP to exclude the header
Use CHOOSECOLS to extract only the necessary columns
Structuring it this way allows you to separate the "source data" from the "processed data".
However, CHOOSECOLS with fixed column numbers is affected by changes in the source data's column structure. If the column order changes in files received every month, consider alternative methods such as Power Query.
