Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

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

  1. #1
    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. #2
    Join Date
    11th February 2003
    Location
    Near the Land of OZ
    Posts
    1,591

    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.

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3.28.2008)

  3. #3
    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. #4
    Join Date
    11th February 2003
    Location
    Near the Land of OZ
    Posts
    1,591

    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.

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3.28.2008)

  5. #5
    Join Date
    19th November 2004
    Posts
    8

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

    Quote Originally Posted by shades
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 10
    Last Post: November 15th, 2013, 06:27
  2. Convert Multiple Columns To Single Rows
    By Ryalut in forum EXCEL HELP
    Replies: 6
    Last Post: June 29th, 2010, 06:46
  3. Convert Single Column To Multiple Columns Of X Rows
    By supratwinturbo in forum EXCEL HELP
    Replies: 5
    Last Post: February 24th, 2008, 12:18
  4. Convert Row To Multiple Columns
    By Estatic in forum EXCEL HELP
    Replies: 9
    Last Post: May 20th, 2007, 14:42
  5. convert rows to columns in EXCEL. Thanks a lot.
    By sunnystone2001 in forum Excel and/or Access Help
    Replies: 1
    Last Post: November 19th, 2004, 09:40

Bookmarks

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