To extract the middle column of a range in Excel for Microsoft 365, enter =CHOOSECOLS(A2:E100,ROUNDUP(COLUMNS(A2:E100)/2,0)) in an empty cell; it returns column C from the five-column range and spills the results downward. For an even number of columns, decide whether you mean the left-middle or right-middle column, because there is no single center column.
What “middle column” means in Excel
Middle-column extraction usually means returning one entire column from a wider rectangular range, such as taking the third column from a five-column report. The selected range, not the worksheet, defines the middle: if the range is B2:F50, its center is D, even though D is not necessarily the center of the worksheet or of the full dataset.
Be precise about the range before choosing a formula. A range with five columns has one center column; a range with four or six columns has two equally central columns. Also consider whether the first column is a label column that should be excluded. If you want the middle of the data columns only, select a range that excludes any row numbers, names, or other fields that should not count.
This is different from extracting characters from the middle of a text string. For text such as an ID code, Excel’s MID function is relevant; for a column of cells, use a column-selection method such as CHOOSECOLS or INDEX.
Extract a middle column with CHOOSECOLS
CHOOSECOLS returns specified columns from an array or range. COLUMNS counts how many columns are in the selected range, and ROUNDUP calculates the left-middle position when the count is even. In Microsoft 365, the returned column spills into cells below the formula automatically.
With the source data in A2:E100, where A:E are the five columns to consider, enter this formula in an empty cell outside the source range:
=CHOOSECOLS(A2:E100,ROUNDUP(COLUMNS(A2:E100)/2,0))
The range has five columns, so COLUMNS returns 5 and ROUNDUP(5/2,0) returns 3. CHOOSECOLS therefore returns the third column of the selected range, which is column C. If the selected range were B2:F100 instead, the same calculation would return the third column within that range, which is column D.
CHOOSECOLS is available in Microsoft 365 and Excel 2024. It is useful when the range may change width, since the formula calculates the middle position instead of requiring you to edit a column number by hand. Keep the range references consistent in both places in the formula, or the count may describe a different range from the one being returned.
Choose the left or right middle for an even range
For an even number of columns, decide on a convention before extracting. For six columns, the two central positions are 3 and 4. ROUNDUP(column count divided by two) selects the left-middle position; the next position selects the right-middle position.
With source data in A2:F100 and all six columns included, this formula returns the left-middle column, the third column of the range (column C):
=CHOOSECOLS(A2:F100,ROUNDUP(COLUMNS(A2:F100)/2,0))
With the same A2:F100 layout, this formula returns the right-middle column, the fourth column of the range (column D):
=CHOOSECOLS(A2:F100,ROUNDUP(COLUMNS(A2:F100)/2,0)+1)
For an odd column count, both sides are not applicable because there is only one center. The second formula adds one regardless, so use it only when the range has an even number of columns and the right-middle choice is intended. A formula cannot infer which interpretation you prefer.
Use INDEX as a compatible alternative
If CHOOSECOLS is unavailable in your Excel version, INDEX can return a column by its position within a range. In Microsoft 365, an INDEX formula with a row number of zero can return an entire column as a dynamic array. It also works in older Excel versions in array contexts, but the entry method can differ.
With source data in A2:E100, this formula returns the third column of that range in Microsoft 365:
=INDEX(A2:E100,0,3)
The zero tells INDEX to return all rows, and 3 identifies the third column. For the five-column example, that is column C. Unlike the CHOOSECOLS example, this formula does not calculate the center automatically: if you change the range width, you must update the 3 yourself.
To fill results down one row at a time in older Excel, put this formula in the first output row and copy it downward. With source data in A2:E100, the first output formula returns the value from the first source row’s third column:
=INDEX($A$2:$E$100,ROWS($1:1),3)
When copied down one row, ROWS($1:1) becomes ROWS($1:2), returning 2 and therefore the next source row. Continue copying only as far as the source data extends. The dollar signs keep the source range fixed as the formula is filled. This row-by-row approach is useful when you need compatibility or want to place extracted values alongside other formulas rather than rely on a spilled result.
Enter the formula and handle the result
For a dynamic-array formula, select a blank cell where the returned list has room to extend downward, type the formula, and press Enter. Excel displays the first result in the formula cell and fills the cells below it with the remaining values. The cells occupied by that output are called the spill range.
- Choose a destination outside the source data so the result does not overwrite the original values.
- Make sure the cells below the destination are empty. Existing content can block the spill and cause a #SPILL! error.
- Check that the source range includes the intended data rows. For example, A2:E100 excludes the header row in row 1.
- Keep an eye on blank source cells: extraction returns the corresponding blank positions rather than removing those rows.
In Excel for Mac, the formulas work the same way in Microsoft 365, and dynamic arrays also spill into adjacent cells. Excel for the web supports dynamic-array formulas as well. If your Excel uses a regional setting that expects semicolons between function arguments, the formula may need that separator instead of commas; the function logic and selected columns remain the same.
Work with Excel tables and changing data
When data is formatted as an Excel table, its rows can expand as new records are added. A fixed range such as A2:E100 will not automatically include records entered below row 100. For a growing dataset, either update the range or use a suitable structured reference that covers the table columns. CHOOSECOLS can operate on an array, but structured-reference syntax and table column names should match the actual table in the workbook.
Dynamic-array formulas generally need to be placed outside an Excel table because a spill range cannot expand inside the table’s calculated-column structure. Put the formula in a normal worksheet cell with clear space for the results. If the output must be part of a table, use a copied-down INDEX formula in the table column or convert the result to values after confirming it is correct.
Extraction returns a view of the source values, not an independent snapshot. If the source changes, the formula output updates. To keep a static copy instead, copy the result and use Paste Special > Values. This replaces formulas with the displayed values, so do it only when you no longer need the output to respond to changes in the source.
Common errors and how to fix them
- #NAME? usually means the Excel version does not recognize CHOOSECOLS, or the function name has been mistyped. Use INDEX instead in versions without CHOOSECOLS.
- #SPILL! means Excel cannot place all returned values. Clear cells in the intended spill area, move the formula, or check whether the formula is inside a table.
- The wrong column appears: remember that the position is counted from the left edge of the selected range, not from worksheet column A. For D2:H100, position 3 is F.
- Only one result appears: you may be using an older Excel version or a formula entered into a context that does not support spilling. Try the copied-down INDEX method.
- Headers appear in the output: the source range includes the header row. Start the range at the first data row if you want values only.
Hidden or filtered-out source rows are not automatically removed just because they are hidden or filtered. A column extraction returns the cells in the specified range, including rows that may not currently be visible. If you need only visible rows, that is a separate filtering task; selecting the middle column alone does not apply a visibility filter.
Middle column extraction versus other methods
CHOOSECOLS is the clearest choice when the desired column position should be calculated from the width of a range. INDEX is broadly compatible and offers control over individual row and column positions. Copying and pasting a source column is simpler for a one-time task, but it will not update when the source changes. A formula is preferable when the extracted column needs to stay linked to the original data.
For a permanently known layout, selecting the center column directly can be easier than a dynamic formula: identify the column letter within the selected range and copy it. But that approach is more fragile if someone inserts or removes columns. A formula based on COLUMNS can adapt to a changed range width, though the selected range itself must still include the correct fields.
Do not use MID just because the request says middle. MID extracts a specified number of characters from text, not a worksheet column. For example, it can take characters from an account code in one cell; it cannot select the center field from a five-column table.
Frequently Asked Questions
How do I extract the middle column from a range in Excel?
In Microsoft 365, use CHOOSECOLS with COLUMNS and ROUNDUP, such as =CHOOSECOLS(A2:E100,ROUNDUP(COLUMNS(A2:E100)/2,0)) for the center of five columns. The result spills down from the formula cell.
What if my range has an even number of columns?
There are two central columns. For six columns, positions 3 and 4 are the left-middle and right-middle choices. Decide which one you need and use that position; there is no universal single middle column in an even-width range.
Can I extract the middle column without CHOOSECOLS?
Yes. Use INDEX, for example =INDEX(A2:E100,0,3) to return the third column of a five-column range in Microsoft 365. In older Excel, a copied-down INDEX formula can return one row at a time.
Why does my formula show #SPILL!?
Excel needs empty cells below the formula to display the full extracted column. Clear obstructing cells, move the formula to an open area, and avoid placing a spilling formula inside an Excel table.
Does extraction remove blank rows or filtered-out records?
No. Selecting a column returns its corresponding cells, including blanks and rows hidden by filtering. Removing blanks or returning only matching or visible records requires an additional operation.
Related
- Tableau Export to Excel: Steps, Limits, and Options
- Python Pandas Excel to CSV: Steps, Options, and Fixes
- Python Pandas Excel to Dictionary: Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Function to Keep a Cell Constant in Formulas ExplainedTo keep a cell reference fixed when you copy an Excel formula, make it absolute by adding dollar…
- How to Filter in Excel and Find the Exact Rows You NeedTo filter a list in Excel, select a cell in your data, choose Data > Filter, then open…
- Round Excel Numbers Without a Formula: Display or ChangeYes. In Excel for Microsoft 365 on Windows, use a number format to display fewer decimal places without…
- Excel Date and Time: Formatting, Formulas, and FixesExcel stores dates as serial numbers and times as fractions of a day, so you can format them…
- Excel Formula Cell Reference Lock: Absolute ReferencesTo lock a cell reference in an Excel formula, add dollar signs before the column letter, row number,…
- SUMIF Formula Criteria Examples for Excel: Rules and TipsUse Excel’s SUMIF function to add values that match one condition, such as a product name, a minimum…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.