Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Apply one formula to multiple cells/Convert rows to columns in EXCEL.

1. I agreed to these rules
Join Date
19th November 2004
Posts
8

## Apply one formula to multiple cells/Convert rows to columns in EXCEL.

I don't know if it is the right place to post this question, but I know many people here are good at both Access and Excel. The question is:

In an excel sheet,
A1: Smith, John
A2: 111 Pine St.
A3: San Diego, CA
A4: (555) 128-549
A5: Jones, Sue
A6: 222 Oak Ln.
A7: New York, NY
A8: (555) 238-1845
A9: Anderson, Tom
A10: 333 Cherry Ave.
A11: Chicago, IL
A12: (555) 581-4914

2. Type the following formula in cell C1:

=OFFSET(\$A\$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

3. Fill this formula across to column F, and then down to row 3.

4.Note that the data is now displayed in cells C1 through F3 as follows:
Smith, John 111 Pine St. San Diego, CA (555) 128-549
Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845
Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914

This is exactly what I want. And I can do step 3 manually to copy that formula to each cell. But,
My question is in step 3. If I have thousands of lines, how can I fill that formula to thousands cells? from C1-C1000, F1-F1000.

I really appreciate it. Thanks.

Excel Video Tutorials / Excel Dashboards Reports

2. Resident Old Codger
Join Date
11th February 2003
Location
Near the Land of OZ
Posts
1,563

## Re: Apply one formula to multiple cells/Convert rows to columns in EXCEL.

Quick way: when you select Cells C1:F1, then move cursor to lower right corner of cell F1, it will turn into a crosshair. Then double-click, and it will fill down as far as you have data in Col. B.

3. I agreed to these rules
Join Date
19th November 2004
Posts
8

## Re: Apply one formula to multiple cells/Convert rows to columns in EXCEL.

Thanks for your reply, but I can't figure it out. I drag the mouse from c1 to f1 and they are highlighted now. But, when I move cursor to lower corner of cell f1, then double click, nothing happens. When I copy the cormula to it, only c1 get the data, not all cells.

Excel Video Tutorials / Excel Dashboards Reports

4. Resident Old Codger
Join Date
11th February 2003
Location
Near the Land of OZ
Posts
1,563

## Re: Apply one formula to multiple cells/Convert rows to columns in EXCEL.

Okay, I assumed you had filled the formula from C1 to F1. If you haven't done that, then select C1, and move to lower right corner and when the cursor changes to crosshairs, then click and drag to the right to column F. Then click outside that range. Select cells C1:F1, and follow the process noted above for double-clicking the crosshair.

5. I agreed to these rules
Join Date
19th November 2004
Posts
8

## Re: Apply one formula to multiple cells/Convert rows to columns in EXCEL.

Okay, I assumed you had filled the formula from C1 to F1. If you haven't done that, then select C1, and move to lower right corner and when the cursor changes to crosshairs, then click and drag to the right to column F. Then click outside that range. Select cells C1:F1, and follow the process noted above for double-clicking the crosshair.
I got it. Thank you very much.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno