Home

copy formula on a huge spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-753272%22%20slang%3D%22en-US%22%3Ecopy%20formula%20on%20a%20huge%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753272%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20is%2040%2C000%20rows%20longs.%26nbsp%3B%20A%20simple%20formula%20is%20on%20line%201.%26nbsp%3B%20I%20want%20to%20copy%20this%20all%20the%20way%20down%20the%20column%20to%20the%20last%20row.%26nbsp%3B%20%26nbsp%3BI%20know%20how%20to%20drag%20it.%26nbsp%3B%20But%20dragging%20for%2040%2C000%20rows%20is%20too%20long!%26nbsp%3B%20%26nbsp%3B%20Is%20there%20a%20way%20to%20have%20excel%20repeat%20it%20every%20row%20right%20to%20the%20end%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethx%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-753272%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753285%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20formula%20on%20a%20huge%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753285%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375749%22%20target%3D%22_blank%22%3E%40kathyh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDouble%20click%20the%20small%20square%20at%20the%20bottom%20right%20edge%20of%20the%20cell%20with%20the%20formula%20you%20want%20to%20copy.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753643%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20formula%20on%20a%20huge%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375749%22%20target%3D%22_blank%22%3E%40kathyh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20suggest%20first%20following%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3Esuggestion%20of%20selecting%20the%20cell%20with%20the%20formula%2C%20then%20doubleclicking%20the%20little%20green%20square%20at%20bottom%20right%20corner%20of%20the%20selection%20marquee.%20It%20will%20stop%20copying%20down%20when%20it%20encounters%20the%20first%20blank%20cell%20in%20the%20column%20to%20its%20left.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EShould%20that%20approach%20not%20take%20you%20down%20all%2040%2C000%20rows%2C%20the%20next%20easiest%20way%20of%20copying%20down%20the%20formula%20is%20to%20use%20the%20Address%20Bar%20just%20above%20the%20intersection%20of%20the%20row%20numbers%20and%20column%20letters.%20Type%20the%20range%20of%20cell%20addresses%20where%20you%20need%20the%20formula%20(e.g.%20B2%3AB40000)%2C%20then%20hit%20Enter.%20This%20will%20select%20all%20those%20cells%20for%20you.%20Next%2C%20type%20the%20formula%20in%20the%20formula%20bar%2C%20and%20hit%20CTRL%20%2B%20Enter.%20That%20will%20copy%20down%20the%20formula%20in%20all%20those%20cells%2C%20making%20adjustments%20for%20any%20cell%20addresses%20with%20relative%20addresses%20for%20row%20numbers%20(i.e.%20no%20%24).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753873%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20formula%20on%20a%20huge%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375749%22%20target%3D%22_blank%22%3E%40kathyh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20collect%20methods%2C%20slight%20modification%20of%20what%20Brad%20suggested%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20type%20in%20B2%20value%2Fformula%20and%20stay%20on%20B2%3C%2FP%3E%0A%3CP%3E-%20in%20Address%20Bar%20type%20B40000%3C%2FP%3E%0A%3CP%3E-%20Shift%2BEnter%3C%2FP%3E%0A%3CP%3E-%20Ctrl%2BD%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
kathyh
Occasional Visitor

I have a spreadsheet that is 40,000 rows longs.  A simple formula is on line 1.  I want to copy this all the way down the column to the last row.   I know how to drag it.  But dragging for 40,000 rows is too long!    Is there a way to have excel repeat it every row right to the end?

 

thx

 

3 Replies

@kathyh 

Double click the small square at the bottom right edge of the cell with the formula you want to copy. 

@kathyh 

I suggest first following @Twifoo suggestion of selecting the cell with the formula, then doubleclicking the little green square at bottom right corner of the selection marquee. It will stop copying down when it encounters the first blank cell in the column to its left.

 

Should that approach not take you down all 40,000 rows, the next easiest way of copying down the formula is to use the Address Bar just above the intersection of the row numbers and column letters. Type the range of cell addresses where you need the formula (e.g. B2:B40000), then hit Enter. This will select all those cells for you. Next, type the formula in the formula bar, and hit CTRL + Enter. That will copy down the formula in all those cells, making adjustments for any cell addresses with relative addresses for row numbers (i.e. no $).

@kathyh 

 

If collect methods, slight modification of what Brad suggested

 

- type in B2 value/formula and stay on B2

- in Address Bar type B40000

- Shift+Enter

- Ctrl+D