How To Use OFFSET To Convert Data Range To Column In Excel

Here is how I used OFFSET and 5 other functions to convert a data range into a single column in Excel:

You can download the file here to follow along. If you get a preview, look for the download arrow in the upper right hand corner.

CORRECTION TO VIDEO: I used 6 functions, not 7 in this formula!

This process is quite a complex one and I strongly encourage you to watch the video to get the full explanation. Below I will give you the basics that I used to achieve this.

The scenario is that I have a data range:

off1

I want to create a formula to convert it to a single column:

off2

I will use this list of functions:

off3

To produce this formula:

=OFFSET($E$1,MOD(ROW(A1)-1,COUNTA(E:E)),(ROUNDUP(ROWS($A$1:A1)/(COUNTA(E:E)),0)-1))

Now, go watch the video and see how I did it (please….)!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , , ,

3 Responses to How To Use OFFSET To Convert Data Range To Column In Excel

  1. Dritan September 1, 2015 at 4:58 am #

    You have done great work by this tutorial.
    Here is my question:

    In the data range some times is not full with data, so some cell are blank.
    How can I deal, because Convert of Data Range To Column is coming in error?

    Any help please?
    Thank you
    Dritan

    • Michael Rempel September 1, 2015 at 7:47 am #

      I’m not sure why you are getting an error. As I noted in the beginning of the video, this concept only works if all the column heights are the same. Any place there is a blank within the data should return a “0”. Can you send me an example of where you are getting an error? One option with varying length of columns is to make sure the two COUNTA formulas reference the column with the most items in it. In my example I used E:E, but you can change that to any other column. Then it should just return a “0” whenever there is no data either within the column or at the end of a column. Again, you can send me an example of your issue to mrempel@excel-bytes.com and I will look at it.

Trackbacks/Pingbacks

  1. Convert A Vertical List to a Data Range In #Excel | Excel Bytes - May 13, 2015

    […] The previous post to convert a Data Range to a list can be found here. […]

Leave a Reply