Others

How to Insert a New Row Below Each Excel Row Using a Column B Value

Camila Milosovich Sep 30, 2026 868 views

Question details

The user needs to insert a new row below each existing row in a dataset and move the value from Column B of the original row into Column A of the newly created row.

How to Insert a New Row Below Each Excel Row Using a Column B Value

Product: Excel
Device & OS: not provided
Scenario: Restructuring an existing dataset by splitting data points from a single row across two vertically stacked rows without disrupting the rest of the data.
Observed behavior: The user requires a systematic way to duplicate rows and shift specific column values to reorganize the dataset effectively.

Before you start

Ensure your dataset has a unique row identifier (such as an Index or Row ID column) and create a backup of your original spreadsheet before running any database queries or scripts.

Solution 1Recommended

Use a SQL UNION Query to Restructure the Data

This method utilizes a database query to combine your original rows with newly generated rows, then sorts them using a sequential identifier to interleave the data perfectly.

If you are managing your Excel data within a database environment (like SQLite, Access, or PostgreSQL), a SQL query is the most robust way to restructure rows. By using a UNION ALL statement, you can append a generated set of rows to your original dataset and sort them so they alternate.

The query relies on appending a suffix to the row identifier so that the original row and the new row stay grouped together when sorted.

Prepare your data table

Ensure your imported spreadsheet data is in a table (e.g., 'Sheet1') and contains a unique identifier column, such as 'rowid', alongside your data columns (e.g., 'f01' for Column A, 'f02' for Column B).

Write the first part of the query

Start a WITH clause to select the original rows. Append an 'a' to the rowid to designate it as the primary row: SELECT rowid || 'a' AS old_rowid, f01, f02 FROM Sheet1.

Write the UNION ALL statement

Combine the first selection with the new rows. Append a 'b' to the rowid, and place 'f02' (Column B's value) into the first column position: UNION ALL SELECT rowid || 'b', f02, '' FROM Sheet1.

Execute the sort query

Wrap the combined sets in a main selection query and order them by the modified identifier: SELECT * FROM a ORDER BY old_rowid;

Export back to your spreadsheet

Once the query executes successfully, export the newly structured dataset as a CSV or Excel file and open it in your spreadsheet software.

Use a SQL UNION Query to Restructure the Data

Database Schema Adaption: The exact SQL query must be adapted to match your specific database schema, table name, and column headers. The example provided uses generic names like 'f01' and 'Sheet1'.

Advanced Data Formatting with WPS

Easily Manipulate and Reformat Rows with WPS Spreadsheet Macros

While SQL queries work well for databases, you can achieve this exact row insertion and data shifting directly within WPS Spreadsheet using its powerful built-in Macro editor, eliminating the need to export your data.

1. Open the Macro Editor: Open your dataset in WPS Spreadsheet, navigate to the 'Tools' tab, and click on 'Macro' (or press Alt + F11) to open the VBA editor.
2. Create a new script module: Right-click your workbook in the project explorer, select 'Insert', and choose 'Module'. This will give you a blank canvas to write your script.
3. Write the insertion loop: Write a 'For' loop that iterates backwards from the last row of your data up to the first row (e.g., For i = LastRow To 1 Step -1).
4. Insert the row and map the value: Inside the loop, use the command 'Rows(i + 1).Insert' to create a blank row below the current row. Then, use 'Cells(i + 1, 1).Value = Cells(i, 2).Value' to copy the Column B value into the new row's Column A.
5. Run the macro: Click the 'Run' button (or press F5). Your spreadsheet will automatically insert the rows and shift the data without needing any external database queries.

Built-in VBA and JS Macro support for complex row manipulation and automation.Seamlessly compatible with Microsoft Excel (.xlsx, .xls) formats and formulas.Free, lightweight, and features a user-friendly interface for handling large datasets.Eliminates the need to export spreadsheet data to external SQL databases.

microsoft office alternative - wps office

Frequently Asked Questions

Can I insert alternate blank rows without using SQL or Macros?

Yes. You can add a 'Helper' column to your data and number the rows sequentially (1, 2, 3...). Then, copy those sequential numbers and paste them beneath your dataset in the same helper column. Finally, sort the entire dataset by the helper column. This will interleave blank rows between your existing rows.

Will inserting new rows disrupt my existing formulas?

If you use a SQL query and export the results, your formulas will be converted to static values. If you use a Macro in WPS Spreadsheet or Excel to insert rows, your relative formula references will automatically adjust as the cells are pushed down.

Why use a generated row identifier like 'a' and 'b' in the SQL query?

Appending 'a' and 'b' to a unique row ID ensures that the SQL engine knows exactly how to pair and order the rows. When the final output is sorted alphabetically by this new identifier, 'Row1b' will naturally fall immediately below 'Row1a'.

Office Quick Solutions