Function Problems

How to Extract Every Sixth Column into One Column in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to extract values that are located every six columns apart on a worksheet (e.g., daily totals) and return them into a single column without entering each cell reference manually.

Product: Excel
Device & OS: not provided
Scenario: Compiling daily totals from a monthly worksheet where the target data points are spaced out at regular 6-column intervals.
Observed behavior: Currently requiring manual cell referencing, the goal is to use an automated formula to efficiently pull spaced-out row data and display it vertically in one column.

Before you start

Verify the exact starting row of your data and count how many values you need to extract in total (such as 31 for a full month).

Solution 1Recommended

Use CHOOSECOLS, SEQUENCE, and TRANSPOSE (Microsoft 365)

This modern dynamic array method allows you to easily extract and transpose evenly spaced columns in a single step.

If you are using Microsoft 365 or a newer version of Excel that supports dynamic arrays, this combination of functions will automatically spill the results into the adjacent cells without needing to copy the formula down.

Select Destination Cell

Click on the cell where you want your new column of extracted data to begin.

Enter the Formula

Type the formula: =TRANSPOSE(CHOOSECOLS('Jan 25'!17:17,SEQUENCE(,31,6,6)))

Execute Formula

Press Enter. The formula will automatically extract 31 values, spaced 6 columns apart from row 17, and list them vertically.

Formula Breakdown: The SEQUENCE function generates 31 numbers starting at 6 and increasing by 6. CHOOSECOLS retrieves those specific columns, and TRANSPOSE converts the row output into a vertical column.

Solution 2

Use INDEX and ROW Functions

For older versions of Excel that do not support dynamic arrays, the INDEX and ROW combination is a highly reliable alternative.

WPS Spreadsheet Solutions

Easily Extract and Manage Spaced Data with WPS Spreadsheet

WPS Office Spreadsheet provides full support for advanced lookup formulas and dynamic arrays, making data extraction and reporting incredibly simple without complicated manual work.

1. Open your Workbook: Launch WPS Spreadsheet and open the monthly totals workbook.
2. Select Destination: Click on the blank cell where you want the vertical list to start.
3. Input Formula: Enter the =INDEX or dynamic array formula as described in the solutions above.
4. Generate Results: Press Enter to instantly compile your evenly spaced data into a clean, single column.

Seamlessly open and edit Microsoft Excel (.xlsx) files with complete format compatibility.Fully supports advanced array functions like SEQUENCE, CHOOSECOLS, and TRANSPOSE.Lightweight, fast, and completely free to use for your daily data management tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I extract every 5th column instead of every 6th column?

Yes. In the SEQUENCE function, change the step value from 6 to 5 (e.g., SEQUENCE(,31,5,5)). For the INDEX formula, change the multiplier at the end from 6 to 5.

Why is my dynamic array formula returning a #SPILL! error?

A #SPILL! error occurs when the formula attempts to output the extracted values, but there is existing data blocking the cells below it. Clear the cells beneath your formula to resolve the issue.

How do I ignore blank cells when extracting every nth column?

You can wrap your extraction formula in the TOCOL function and use its optional parameter to ignore blanks. For example: =TOCOL(CHOOSECOLS('Jan 25'!17:17,SEQUENCE(,31,6,6)), 1).

Can I extract data from a specific range instead of the entire row?

Absolutely. You can change the reference '17:17' to a specific range like 'F17:AA17'. Make sure to adjust your SEQUENCE start value to '1' instead of '6' since the range now begins exactly at your first data point.

Excel & Data Problems