Data Import & Export

How to Move Recorded Scores into Multiple Excel Columns Automatically

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to distribute recorded values into corresponding Score 1, Score 2, and Score 3 columns in a large spreadsheet automatically.

Product: Spreadsheet
Device & OS: not provided
Scenario: Reorganizing a large dataset where scores listed in rows or a single column must be shifted across multiple designated columns without manual typing.
Observed behavior: Data is currently grouped or recorded in a single column format, making it tedious and inefficient to manually type each score into the separate target columns.

Before you start

Ensure your source data is sorted logically (e.g., by ID or student name) so the formulas can accurately group the corresponding scores together.

Solution 1Recommended

Use TRANSPOSE and IF Formulas for Sequential Data

This solution is best when your recorded scores appear in a fixed sequential pattern in your dataset.

By combining the IF and TRANSPOSE functions, you can extract a block of row-based data and lay it out across multiple columns automatically.

Enter the formula

Assuming your data begins in A1, select the first blank cell under your Score 1 column (e.g., G4). Enter the formula =IF(A4<>A3,TRANSPOSE(F4:F6),"").

Apply formula to the column

Press Enter, then click and drag the fill handle at the bottom right of the cell to copy the formula down the entire column.

Convert formulas to values

Highlight the newly generated score columns, right-click, select 'Copy', then right-click again and choose 'Paste Special > Values'.

Clean up the spreadsheet

Apply a filter to the dataset to isolate the blank rows left behind by the IF formula, and delete those rows to finalize your layout.

Pro Tip: Pasting as values is a critical step; it disconnects your new layout from the original raw data so you can safely delete unwanted rows.

Solution 2

Use Dynamic Array FILTER and TRANSPOSE Formulas

This method utilizes dynamic arrays to actively filter the dataset based on unique identifiers, making it highly robust for modern spreadsheet versions.

Powerful Spreadsheet Tool

Easily Manipulate and Transpose Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like TRANSPOSE and FILTER, allowing you to effortlessly reorganize large datasets across multiple columns without tedious manual entry.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the .xlsx or .csv file containing your recorded scores.
2. Apply the TRANSPOSE formula: Click on the destination cell for your first score and enter the TRANSPOSE combined formula to spill the data across columns.
3. Paste as values: Select the generated data, click 'Copy', and use the 'Paste Special' button in the Home ribbon to paste only the values.
4. Filter out blank rows: Navigate to the Data tab, click 'AutoFilter', deselect your valid data to reveal only blanks, and delete those rows.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Native support for dynamic array functions to manipulate data instantly.Built-in Paste Special and Data Filtering tools for rapid data cleanup.Free, lightweight, and user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #VALUE or #NAME error when using the TRANSPOSE formula?

If you are using an older version of your spreadsheet software that doesn't support dynamic arrays, you may need to press Ctrl+Shift+Enter to execute TRANSPOSE as an array formula. Alternatively, ensure functions like FILTER are supported in your current version.

Can I use the 'Text to Columns' feature instead of formulas?

Text to Columns is ideal when multiple scores are trapped inside a single cell, separated by commas or spaces. If your scores are distributed vertically across different rows, using TRANSPOSE formulas or Power Query is the correct approach.

What is the purpose of Paste Special > Values?

Formulas dynamically pull data from source cells. If you delete the source cells or the blank rows, the formula will break. Pasting as values replaces the underlying formula with the calculated static text or number, preserving your data.

How do I quickly delete all blank rows after pasting my values?

Select your entire dataset, go to the Data tab, and click the Filter icon. Click the drop-down arrow on your Score 1 column, uncheck '(Select All)', and check only '(Blanks)'. Highlight all the visible empty rows, right-click, and select 'Delete Row'.

Excel & Data Problems