Convert Column to Table in Excel


Copy and paste is pretty simple and easy to do, but it is sometimes more error prone than writing a function to do it (that assumes the function written does what it is supposed to do).

I recently extracted a column of data that I want to convert into a table (matrix) for comparison across replicates. In STATA or R, this is the task called reshape. Since I have exported the data into our favorite spreadsheet software, I would like to do this within Excel. A bit of googling landed me to this website http://www.cpearson.com/excel/ColumnToTable.aspx, the article too long and the description too short. But at least it pointed me to “OFFSET”.

What you need is this formula

=OFFSET(REF_CELL, p*COL_AWAY_FROM_REFERENCE+ROW_AWAY_FROM_REFERENCE,0,1,1)

1. Reference Cell

2. How may rows your target cell is from 1): p*COL_AWAY_FROM_REFERENCE+ROW_AWAY_FROM_REFERENCE

3 How many columns your target cell is from 1): 0

4. How wide is the target region from the target cell.: width,height = 1,1

In this case you want to transpose a column with dimension 1xN to nxp (this include the first p rows of the original column data as the first column of the final nxp table).

by pasting this formula to column next to the original data, you will get the 2nd column containing row “p+1” from the original data column.

For example, your original data is from $B$2:$B$100, containing item 1 to 9 (i.e. p =9, of 11 repeated measurements). You want to cut row 11-19 and put it right next to B2:B10. Instead, put this =OFFSET($B$2,9*(COLUMN()-COLUMN($B$2))+(ROW()-ROW($B$2)),0,1,1) in cell C2, then copy it to C2:L2, then copy it cover C10:L10. This will give you a 9 row by 11 column table.

Advertisements

6 thoughts on “Convert Column to Table in Excel”

      1. Yeah, I’d never figure that out myself and it saved me a lot of time. Combined with Paste special transpose function I very easily converted one column of data into a nice table 🙂

  1. Thank you! Very well explained.
    This saved me time and prevented me from otherwise would have been an awful and repetitive task.
    Greetings from Argentina!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.