How to Convert a Single-Column Book List into Two Columns in Excel
Question details
The user needs to transform a single column of data, where entries alternate between book titles and authors, into two distinct columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting and reorganizing a long text list of books to make it easier to read and sort in a spreadsheet.
- Observed behavior
- The data is currently stacked vertically in one column (e.g., A1: Title, A2: Author, A3: Title, A4: Author) and needs to be split horizontally.
Ensure your single-column list alternates consistently without missing entries (Title, Author, Title, Author), as the formula relies on an exact repeating pattern to split the data correctly.
Use WRAPROWS and TOCOL Formulas (Microsoft 365)
Utilize modern dynamic array formulas to instantly reshape your single-column data into a two-column format.
If you are using a modern version of Excel, the WRAPROWS function is the most efficient way to achieve this. Combined with TOCOL, it can safely ignore blank cells and wrap your data into multiple columns.
Click on a blank cell where you want the new two-column table to begin (for example, cell C2).
Type the formula =WRAPROWS(TOCOL(A1:A300,3),2) into the formula bar. Replace A1:A300 with the actual range containing your book list.
Press Enter. The formula will automatically populate the cells to the right and below, organizing the data into alternating columns.
Click the cells directly above your new data (e.g., C1 and D1) and type 'Title' and 'Author' to label your new columns.
Use the INDEX and ROW Functions (Universal Method)
If you are using an older version of Excel or a different spreadsheet software that does not support dynamic arrays, you can use the INDEX function.
Seamlessly Format and Reorganize Data with WPS Spreadsheet
WPS Spreadsheet offers powerful data formatting tools and robust formula capabilities, allowing you to quickly reshape single-column lists into structured tables with zero hassle.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing your single-column book list.
- 2. Apply the formatting formula: Select a blank destination cell and input your desired data reshaping formula, such as WRAPROWS or INDEX.
- 3. Finalize your table: Add descriptive headers like 'Title' and 'Author', then apply a quick table style to make your data presentable and easy to read.
Frequently Asked Questions
What if my single-column list contains empty rows between the books?
If you use the formula =WRAPROWS(TOCOL(A1:A300,3),2), the number '3' inside the TOCOL function automatically ignores any blank cells and errors. This ensures your final two-column table remains perfectly aligned without gaps.
Can I split the single-column list into three or four columns instead of two?
Yes. To split the data into more columns (for example: Title, Author, Year), simply change the last number in the WRAPROWS formula. For three columns, the formula would be =WRAPROWS(TOCOL(A1:A300,3),3).
Why am I getting a #NAME? error when trying to use WRAPROWS?
The #NAME? error typically occurs if you are using an older version of Excel or a spreadsheet program that does not support the newly introduced WRAPROWS function. If you encounter this, switch to the INDEX and ROW combination formula provided in the second solution.
