As the doctor gone rogue

August 17, 2011

Convert Column to Table in Excel

Filed under: data management, excel — hypotheses @ 1:47 pm

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 Comments »

  1. […] 21, 2011 in Uncategorized Last time I wrote about converting column to table in Excel. Today, I have to do this again, and went back to read what I wrote. One of the new problems that I […]

    Pingback by Converting Column Data to Table (2) « Not A Mad Scientist….. — November 21, 2011 @ 6:03 pm

  2. This was extremely helpful! Thanks a lot 🙂

    Comment by n0x1k — March 6, 2014 @ 10:17 am

    • I’m glad you find it helpful.

      Comment by hypotheses — March 6, 2014 @ 10:56 am

      • 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 🙂

        Comment by n0x1k — March 6, 2014 @ 11:13 am

  3. 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!

    Comment by Emiliano Alexander — October 7, 2016 @ 3:46 am

  4. Thank you – this really helped me convert a long column with hourly readings (8,760 rows) for a whole year into a nice 12 x 24 matrix

    Comment by Mayank Bhargava — January 19, 2017 @ 5:11 am


RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: