Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 93 ยท Lookup and Reference

ROWS, COLUMNS, TAKE, DROP, CHOOSECOLS and Reshaping Data

Cut, pick, stack and reshape ranges with ROWS, COLUMNS, TAKE, DROP, CHOOSECOLS, CHOOSEROWS, HSTACK, VSTACK, WRAPROWS and TOCOL, with runnable stand-ins.

Lookups return one value. Often you want a whole block: the first three rows of a list, columns A and D without the columns between, two tables glued into one, or a single column turned into a grid. Older Excel needed INDEX tricks, helper columns or copy and paste for this. Microsoft 365 has dedicated array functions that take a range or array and return a reshaped array that spills into the cells below and to the right.

This lesson covers the size functions ROWS and COLUMNS and the reshaping family: TAKE, DROP, CHOOSECOLS, CHOOSEROWS, HSTACK, VSTACK, WRAPROWS and TOCOL. Excel cannot run on this site, so the formulas are shown read-only and each one is paired with a JavaScript stand-in and a pandas equivalent that you can run. Spilling itself is explained in Dynamic Arrays and Spilling; here it is enough to know that one formula can fill many cells.

TAKE, DROP, CHOOSECOLS, CHOOSEROWS, HSTACK, VSTACK, WRAPROWS and TOCOL exist in Microsoft 365 and Excel for the web. Older perpetual versions such as Excel 2019 do not have them, so check before you send a workbook to someone else.

A reshaping toolbox

TAKE / DROP Keep or remove rows and columns at the edges.
CHOOSE Pick exact columns or rows, in any order.
STACK Glue arrays side by side or on top.
WRAP / TOCOL Turn a list into a grid, or back.

Every one returns an array that spills.

1 / 2

Counting: ROWS and COLUMNS

ROWS(array) returns how many rows a range or array has, and COLUMNS(array) returns how many columns. They are not volatile and they are the building blocks for formulas that adapt to the size of the data.

TEXT
=ROWS(A2:C10)        9
=COLUMNS(A2:C10)     3
=ROWS(A2#)           the height of a spilled array that starts in A2

The # after a cell address means "the whole spill range of the formula in that cell". Use these two functions rather than typing numbers such as 9 into other formulas, so the result stays correct when the data grows.

TAKE and DROP: keep or remove edges

TAKE(array, rows, [columns]) keeps only some rows and columns. A positive number counts from the start (top or left), a negative number from the end (bottom or right). You may leave rows empty to work with columns only. DROP is the opposite: it removes the given rows and columns and returns the rest.

TEXT
=TAKE(A1:D5, 3)          first 3 rows, all columns
=TAKE(A1:D5, -2)         last 2 rows
=TAKE(A1:D5, , 2)        all rows, first 2 columns
=DROP(A1:D5, 1)          everything except the header row
=DROP(A1:D5, , -1)       everything except the last column

DROP(data, 1) is the standard way to remove a header row inside a formula. Asking TAKE for zero rows gives a #CALC! error, because the result would be an empty array.

CHOOSECOLS and CHOOSEROWS: pick exactly what you want

CHOOSECOLS(array, col1, [col2], ...) returns the columns you list, in the order you list them, and may repeat them. Negative numbers count from the right, so -1 is the last column. CHOOSEROWS does the same for rows.

TEXT
=CHOOSECOLS(A1:D5, 1, 3)     columns A and C
=CHOOSECOLS(A1:D5, 4, 1)     column D first, then column A
=CHOOSEROWS(A1:D5, 1, -1)    the first and the last row

An index of 0, or one beyond the array, gives #VALUE!. These functions replace the old pattern INDEX(range, 0, {1,3}) and are much easier to read.

HSTACK and VSTACK: glue arrays together

HSTACK(array1, array2, ...) places arrays side by side. VSTACK places them one above the other. If the pieces have different sizes, the gaps are filled with #N/A. You can wrap the result in IFNA to replace those gaps.

TEXT
=HSTACK(A2:A10, C2:C10)         two separate columns become one block
=VSTACK(A1:C1, SORT(A2:C20))    a header row on top of sorted data
=VSTACK(Jan!A2:C10, Feb!A2:C10) combine two sheets' tables

Stacking is how you add a header back to a result, or consolidate data from several sheets without copy and paste. For clean-up on a bigger scale, see Power Query, which does the same job as a repeatable query.

WRAPROWS and TOCOL: change the shape

WRAPROWS(vector, wrap_count, [pad_with]) cuts a single row or column into rows of wrap_count items. If the last row is short, it is padded with #N/A unless you give pad_with. WRAPCOLS fills column by column instead.

TOCOL(array, [ignore], [scan_by_column]) does the reverse: it flattens a block into one column. ignore is 0 to keep everything (the default), 1 to skip blanks, 2 to skip errors and 3 to skip both. By default the block is read row by row; pass TRUE as the third argument to read column by column. TOROW makes a row instead of a column.

TEXT
=WRAPROWS(A2:A11, 3, "")     ten items in rows of three, pad with empty text
=TOCOL(B2:D10, 1)            all values of B2:D10 in one column, blanks removed
=TOCOL(B2:D10, 1, TRUE)      same, but reading down each column first

TOCOL is also the quickest way to feed a two-dimensional block into UNIQUE or a count.

Run it: the functions as JavaScript

The stand-in below treats a table as an array of rows and implements the functions as described. It is our model of the behavior, with simplified error handling (thrown errors are shown as the error code).

JS Reshaping functions as JavaScript stand-ins

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Look at the two VSTACK and WRAPROWS results: the gaps are #N/A, exactly what the real functions produce unless you wrap them in IFNA or give a padding value.

Combining the pieces

The functions become powerful when nested. A top-three report is TAKE(SORT(data, 3, -1), 3); a sorted list with its header put back is VSTACK(TAKE(data, 1), SORT(DROP(data, 1), 3, -1)). The next stand-in builds both with a tiny SORT.

JS Top 3 and a header put back

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The same ideas in pandas

If you work in Python too, each Excel function has a close pandas twin. The next block runs in the real Pyodide runtime with pandas.

Python pandas equivalents of TAKE, DROP, CHOOSECOLS, HSTACK and TOCOL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does =DROP(A1:D5, 1) return?

    DROP removes the given number of rows from the start (or the end when negative) and returns what is left. DROP(range, 1) is the usual way to strip a header row.
  2. 2. In =TAKE(A1:D5, -2), what does the minus sign mean?

    Negative counts start at the bottom (or the right edge for columns). TAKE(A1:D5, -2) returns the last two rows.
  3. 3. You stack a 2-row array on top of an array that is 3 columns wide using VSTACK. What fills the missing cells?

    VSTACK and HSTACK pad shorter arrays with #N/A. Wrap the formula in IFNA to replace the gaps.
  4. 4. Which function turns a block of cells into a single column and can skip blanks?

    TOCOL flattens a block into one column; its ignore argument set to 1 skips blanks (2 skips errors, 3 skips both).
  5. 5. Why prefer CHOOSECOLS(data, 4, 1) over copying columns by hand?

    The result is a formula, so it follows changes in the source. It does need a Microsoft 365-era version of Excel.

Summary

  • ROWS and COLUMNS return the size of a range or array and keep formulas independent of hard-coded sizes.
  • TAKE keeps and DROP removes rows and columns from the start (positive) or the end (negative).
  • CHOOSECOLS and CHOOSEROWS pick positions in any order, with negative numbers counting from the end.
  • HSTACK and VSTACK glue arrays together and pad short ones with #N/A.
  • WRAPROWS reshapes a list into a grid; TOCOL and TOROW flatten a grid, optionally skipping blanks or errors.
  • These functions need Microsoft 365 or a version of the same generation, and results spill.

Next: Text Functions: LEFT, RIGHT, MID, LEN, TRIM.