How to quickly transpose blocks of data from rows to columns in Excel?
When a single column contains several blocks of data separated by blank cells, you may want to transpose each block into a separate row. The following methods show how to reorganize these data blocks using formulas or Kutools for Excel.
- Transpose blocks of data from rows to columns with formulas
- Transpose blocks of data from rows to columns with Kutools for Excel
- Frequently Asked Questions
| Method | Best for | Setup | Handling different block sizes |
|---|---|---|---|
| Formulas | Users comfortable with array formulas and helper cells | Requires two formulas and manual filling | The formulas must be adjusted when the block size changes |
| Kutools for Excel | Users who want a quick, formula-free solution | Select the range and choose Blank cell delimits records | Automatically recognizes blocks separated by blank cells |
Transpose blocks of data from rows to columns with formulas
You can use an array formula together with a helper-column formula to transpose blocks containing three items each.
- Select three adjacent cells where you want to place the first transposed block, such as C1:E1. Enter the following formula, and then press Ctrl + Shift + Enter:
=TRANSPOSE(OFFSET($A$1,B1,0,3,1))
- In cell B2, enter the following formula, and then drag the fill handle down as far as needed:
=B1+4
- Select C1:E1, and then drag the fill handle down until zeros appear.
📝 Notes:
In the first formula, A1 is the first cell of the source list, B1 is the blank helper cell next to the first source item, and 3 is the number of items in each block.
In the second formula, 4 represents the three data rows in each block plus one blank separator row. Adjust both formulas if your blocks contain a different number of items.
Transpose blocks of data from rows to columns with Kutools for Excel
If you do not want to create and adjust multiple formulas, the Transform Range feature in Kutools for Excel can identify records separated by blank cells and transpose each block into a separate row.
After installing Kutools for Excel, follow these steps:
- Select the data you want to transpose, and then click Kutools > Range > Transform Range.
- In the Transform Range dialog box, select Single column to range, and then select Blank cell delimits records.
- Click OK, and then select the first cell where you want to place the result.
- Click OK. Each block of data is transposed into a separate row.
Demo
Frequently Asked Questions
What separates one data block from another?
In the example, each block contains three data cells followed by one blank cell. The formulas use this fixed structure, while Kutools for Excel uses the blank cells to identify where each record ends.
How can I transpose blocks containing more or fewer than three items?
For the formula method, change 3 in the first formula to the number of items in each block. In the helper formula, replace 4 with the block size plus the number of separator rows. With Kutools for Excel, the blocks can contain different numbers of items as long as blank cells separate them.
Why do I need to press Ctrl + Shift + Enter?
The first formula is an array formula in Excel versions that do not support dynamic arrays. Pressing Ctrl + Shift + Enter applies the formula to all selected output cells at once.
Why do zeros appear after I fill the formulas down?
Zeros appear when the formulas begin referencing blank cells beyond the source data. Stop filling the formulas down once all data blocks have been transposed.
Will the transposed results update when the source data changes?
Formula results update when the referenced source data changes. Results created with Kutools for Excel are static, so run the transformation again if you later change the source data.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in