Data Import & Export

How to Transpose Website Data into Six Excel Columns

Algirdas Jasaitis Oct 13, 2026 870 views

Question details

The user needs to convert raw website data, which has been pasted sequentially into a single column, into a proper data table organized with six columns based on the original headers.

How to Transpose Website Data into Six Excel Columns

Product: Excel
Device & OS: not provided
Scenario: Reorganizing copied website data that pastes poorly into a spreadsheet as a continuous single-column list instead of a structured table.
Observed behavior: The copied data appears continuously in column A, with every block of six cells representing one complete record corresponding to six column headers.

Before you start

Ensure your pasted website data sits uniformly in Column A starting from row 1, and remove any blank cells or unnecessary introductory text above the data so the formula retrieves the correct increments.

Solution 1Recommended

Use the INDIRECT Formula to Transpose Every Six Rows

This solution dynamically pulls every block of six rows from a single column and redistributes them into a standard six-column layout.

By utilizing the INDIRECT function combined with ROW and COLUMN calculations, you can instruct Excel to skip exactly six cells vertically each time you copy the formula down.

This is an ideal method for large datasets where copying and using Paste Special > Transpose repeatedly would be too time-consuming.

Set up your headers

Copy the first six bold entries from your source data (which represent the column headings). Paste them horizontally into a new area, such as cells C1 through H1.

Enter the INDIRECT formula

Select the first empty cell directly below your new headers (e.g., cell C2). Type the formula: =INDIRECT("A"&(6*ROW(A1)-6+COLUMN(A1))) and press Enter.

Fill the formula across

Click the cell containing the formula. Hover over the bottom-right corner to activate the fill handle, then click and drag it horizontally across the next five columns to fill the rest of the record.

Fill the formula down

Keep the entire row of newly calculated cells selected, click the fill handle again, and drag downwards to populate the remaining data records.

Adjusting Cell References: If you insert rows or columns before your source data, you may need to adjust the "A", ROW, or COLUMN references in the formula to point exactly to where the data sequence begins.

Advanced Data Formatting

Organize Website Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions and INDIRECT formulas, making it simple to restructure messy web data. Its seamless Microsoft Excel compatibility means your complex formulas will work flawlessly without missing a beat.

1. Paste raw data: Open WPS Spreadsheet and paste your copied website data directly into Column A.
2. Create column headers: Manually type or copy the six required headers into cells C1 through H1.
3. Apply the calculation: In cell C2, type =INDIRECT("A"&(6*ROW(A1)-6+COLUMN(A1))) and press Enter.
4. Drag to populate: Use the WPS fill handle to drag the formula across the six columns, then drag down to completely reorganize the data block.

Full support for INDIRECT, ROW, and COLUMN formulas for advanced data formatting.100% compatibility with Microsoft Excel (.xlsx and .xls) file formats.Lightweight architecture ensures fast calculation even when processing thousands of transposed rows.Free and user-friendly interface for effortless data cleaning.

microsoft office alternative - wps office

Frequently Asked Questions

What if my data needs to be split into 8 columns instead of 6?

You can easily adapt the formula. Simply replace the number '6' in the formula with '8'. The updated formula would be: =INDIRECT("A"&(8*ROW(A1)-8+COLUMN(A1))).

Why is the formula returning a #REF! error?

A #REF! error typically occurs if the formula is referencing a cell that does not exist or if the source column was moved or deleted. Ensure your raw data is placed precisely in Column A.

Is there a newer Excel function that replaces this method?

Yes, in the latest versions of Excel (Microsoft 365) and compatible spreadsheet software, you can use the WRAPROWS function (e.g., =WRAPROWS(A1:A100, 6)) to achieve the same result instantly without calculating row and column offsets.

Excel & Data Problems