» Transposing an Entire Range of Data into a Single Column
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Problem: Transposing all the values from Data Range (cells B3:E5) into a single column.
Solution:
To process the values in Data Range from left to right (i.e. row by row):
Use the OFFSET, INT, ROW, and MOD functions as shown in the formula in cell H3:
=OFFSET($B$3,INT((ROW()-ROW($H$3))/4),MOD(ROW()-ROW($H$3),4))
To process the values in Data Range from top to bottom (i.e. column by column):
Use the OFFSET, MOD, ROW, and INT functions as shown in the following formula in cell I3:
=OFFSET($B$3,MOD(ROW()-ROW($I$3),3),INT((ROW()-ROW($I$3))/3))
Book Store:
Recommended Books:
- The Complete Book of Business Plans: Simple Steps to Writing a Powerful Business Plan (Small Business Sourcebooks)
- The McGraw-Hill Guide to Writing a High-Impact Business Plan: A Proven Blueprint for First-Time Entrepreneurs
- Flipping Properties: Generate Instant Cash Profits in Real Estate
- How to Pay Zero Taxes (Annual)
- Microsoft Windows XP Registry Guide
- PowerPoint® 2002 For Dummies®
No comments have been submitted.

