+ Reply to Thread
Results 1 to 3 of 3

Copying and pasting a formula into 100,000 cells

  1. #1
    Registered User
    Join Date
    06-03-2020
    Location
    London
    MS-Off Ver
    Windows 10
    Posts
    2

    Copying and pasting a formula into 100,000 cells

    Hi,
    Sorry to be a newb here but I have a tiresome problem

    I have spreadsheets of data that are multi-column and about 100,000 rows each. I need to copy a formula (=sum(A1/B1)*C1 for example) in cell D1 into the 100,000 cells below D1 in column D. I can copy D1 and then highlight D2 and drag into the cells below but this takes a LONG time for 100,000 rows.

    Is there an easier way? Thanks for your help.

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Copying and pasting a formula into 100,000 cells

    Assuming that C1:C100000 (or whatever exactly it is) all have data without blank rows,

    Then you can type the formula in D1,
    And then with the cell D1 selected (but not in in the cell, typing; with the whole cell selected),
    You can hover the mouse icon over the bottom-right corner of the cell. Your icon should change from a "white cross with black border" to a "black cross"; with the mouse icon like that, double-click.

    That should dropfill the contents of the cell down the column.

    It does this by marching down the column, and in each cell, checking the cell to the left and seeing if there's something there; if there is, it pulls the formula from the cell above exactly as if you manually pulled it, and then moves to the next column. If there's nothing to the left, it thinks it's reached the bottom of the table and it's done.

    So if there's a blank cell in C500 or whatever, it might terminate early. There's higher order-logic that gets involved if Excel thinks it's a Table as a particular data entity, so typically I'd bop down to the bottom row and make sure it filled correctly.

    More documentation in this help article, including proper ribbon commands instead of the tooltip shortcut I described above.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Registered User
    Join Date
    06-03-2020
    Location
    London
    MS-Off Ver
    Windows 10
    Posts
    2

    Re: Copying and pasting a formula into 100,000 cells

    Bloody brilliant Ben! THANK YOU. You've saved me a SIGNIFICANT amount o work. Thanks v much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copying and pasting data from cells
    By educationfiend in forum Excel General
    Replies: 5
    Last Post: 11-06-2017, 12:50 PM
  2. Copying and pasting over several Cells via VBA
    By pasqui83 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-05-2017, 03:58 PM
  3. Replies: 0
    Last Post: 05-15-2013, 03:22 AM
  4. Copying & pasting visible cells only
    By Enewbee in forum Excel General
    Replies: 2
    Last Post: 07-20-2012, 02:05 AM
  5. Copying and Pasting Cells
    By JoshF in forum Excel General
    Replies: 3
    Last Post: 01-04-2012, 08:38 PM
  6. Copying and pasting cells with formulas
    By jonvanwyk in forum Excel General
    Replies: 2
    Last Post: 07-01-2010, 12:37 PM
  7. Copying and Pasting set range of cells
    By britboy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-18-2009, 10:50 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1