by Glenn Gutmacher

Q: I have a Microsoft Excel spreadsheet with thousands of names of potential candidates. I don't intend to blind e-mail them, but I do want to process them all over time. The entries are all in a single column, and look like this:

name1

company1

e-mail address1

ID number1

name2

company2

e-mail address2

ID number2

etc.

I can't find anyone who can tell me how to "rotate" this spreadsheet so that I'll end up with 4 columns that look like this:

name1 company1 e-mail address1 ID number1

name2 company2 e-mail address2 ID number2

A: I'm glad you don't plan to mass-email that many, which can get you in trouble with the US federal CAN-SPAM Act. But I do agree you need to change the vertical orientation to horizontal as you indicated in order to send even small batches of messages in any email processing program.

Fortunately, lists like this are easily fixable. The key to doing this simply is the INDIRECT function, which didn't exist with earlier versions of Excel. (If you have Microsoft Office 2007, then you're all set.)

Assuming your list is in column A, with the first name in cell A1, first company in cell A2, etc., put the following in the cells indicated (values beginning with an equals sign are formulas):

cell b1: =INDIRECT("A"&F1)

cell c1: =INDIRECT("A"&F1+1)

cell d1: =INDIRECT("A"&F1+2)

cell e1: =INDIRECT("A"&F1+3)

cell f1: 1

cell b2: =INDIRECT("A"&F2)

cell c2: =INDIRECT("A"&F2+1)

cell d2: =INDIRECT("A"&F2+2)

cell e2: =INDIRECT("A"&F2+3)

cell f2: =F1+4

The cells in B2 through F2 can be copied in one move all the way down (starting with corresponding columns in row 3 down thousands of rows as desired). This will instantly "rotate" (display) the data from column A as you desired.

If you don't want the (now) extraneous data in column A and F, don't delete them or everything in columns B-E will be messed up! Instead, click column headings B-E to highlight the content all the way down, and copy (Ctrl+C). Launch a new blank worksheet in the same file (or a completely new spreadsheet file) and press Paste --> Paste special (not the regular Ctrl+V paste). Select the radio button preceding Values and click OK.

You now have all the records in rows, with the formulas replaced by plain values, and won't be messed up if you sort, move them around, etc.!