How to quickly transpose blocks of data from rows to columns in Excel?
Have you ever suffered a problem about transposing some data from rows to columns without the blocks as below screenshot shown? In this article, I introduce some tricks on handling this job quickly and easily in Excel.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Here you can use some formulas to solve this problem.
1. Select three cells which will place the result, for instance, C1:E1, and type this formula =TRANSPOSE(OFFSET($A$1,B1,0,3,1)), and press Shift + Ctrl + Enter keys. See screenshot:
Tip: in the formula, A1 is the first cell of the data list, B1 is the blank cell next to the data, 3 means block by every three data.
2. In the cell B2, type this formula, =B1+4, and drag the auto fill handle down to cells needed this formula.
Tip: In the formula, B1 is the cell next to the first data of the list you use to transposed, 4 indicates the blocks divide data by every three rows and plus one.
3. Select C1:E1, drag auto fill handle downs until zeros appear. See screenshot:
If the above formulas are complex for you, you can have a try on the Transform Range feature of Kutools for Excel.
|Kutools for Excel, with more than 120 handy functions, makes your jobs easier.|
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Select the data you want to transpose, and click Kutools > Range > Transform Range. See screenshot:
2. In the Transform Range dialog, check Single column to range option and the Blank cell delimits records. See screenshot:
3. Click Ok, and select a cell to place the result. See screenshot:
4. Click OK, then the data has been transposed.