Course / Modern Excel

Reshape a list: TAKE, DROP and CHOOSECOLS

Pull the top rows of a sorted list with TAKE, everything after them with DROP, and only the columns you need with CHOOSECOLS. Intermediate · 5 min · TAKE, DROP, CHOOSECOLS, Nesting dynamic arrays

Once a formula spills a list, three small functions reshape it without any helper columns:

They are at their best wrapped around SORT or FILTER. The sheet is a sales team's results for the quarter.

Steps

  1. Who are the top three? In E2, sort the table by sales, highest first, and keep only the first three rows: =TAKE(SORT(A2:C11, 3, -1), 3)

  2. Now everyone who is not in the top three. In E7, drop the first three rows of the same ranking: =DROP(SORT(A2:C11, 3, -1), 3)

  3. The region is not needed in the summary. In I2, keep only the first and third columns of the top three: =CHOOSECOLS(TAKE(SORT(A2:C11, 3, -1), 3), 1, 3)

Remember

In the function reference: CHOOSECOLS, DROP, SORT, TAKE

Previous: SORTBY, SEQUENCE and LET · Next: Combine lists: VSTACK and HSTACK