Auto Fill columns based on filled cells to the left

%3CLINGO-SUB%20id%3D%22lingo-sub-1558724%22%20slang%3D%22en-US%22%3EAuto%20Fill%20columns%20based%20on%20filled%20cells%20to%20the%20left%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558724%22%20slang%3D%22en-US%22%3E%3CP%3EOlder%20versions%20of%20Excel%20would%20let%20me%20fill%20a%20column%20to%20the%20row%20level%20with%20the%20data%20left%20of%20that%20row.%20Now%20when%20I%20double%20click%20the%20autofill%20handle%20in%20the%20first%20cell%2C%20it%20fills%20all%20the%20way%20down%20to%20match%20the%20numbers%20in%20the%20column%20to%20the%20right.%20Is%20there%20a%20way%20to%20set%20up%20the%20columns%20to%20fill%20only%20to%20the%20level%20of%20those%20numbers%20listed%20to%20the%20left%20of%20the%20column%3F%20I'm%20doing%20a%20worksheet%20that%20lists%20different%20types%20of%20wood%2C%20the%20board%20feet%20and%20the%20cost%20per%20foot.%20I%20then%20want%20to%20calculate%20what%20that%20wood%20will%20cost.%20I%20don't%20want%20to%20autofill%20to%20the%20level%20of%20cells%20with%20the%20formula%2C%20but%20just%20to%20the%20level%20of%20cells%20with%20the%20type%20and%20board%20ft.%20When%20I%20double%20click%20the%20autofill%20handle%20in%20the%20first%20cell%20of%20the%20third%20column%2C%20I%20only%20want%20it%20to%20fill%20to%20the%20point%20where%20the%20first%20two%20columns%20end.%26nbsp%3B%20I've%20attached%20a%20file%20to%20help%20explain%20since%20I%20don't%20know%20if%20I'm%20making%20much%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1558724%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1558772%22%20slang%3D%22de-DE%22%3ESubject%3A%20Auto%20Fill%20columns%20based%20on%20filled%20cells%20to%20the%20left%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558772%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746068%22%20target%3D%22_blank%22%3E%40excel_user_1720170%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EQuickly%2C%20I%20hope%20I%20got%20it%20right%20through%20the%20translation.%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3EPlease%20open%20the%20inserted%20worksheet%3C%2FSPAN%3E.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EIf%20you%20liked%20my%20solution%2C%20please%20mark%20it%20as%20the%20correct%20answer%20...%20if%20possible%20with%20a%20like%20(Thumbs%20up%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eso%20everyone%20else%20can%20be%20informed.%20If%20you%20didn't%20like%20my%20answer%2C%20please%20give%20me%20a%20quick%20feedback.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EThank%20you%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ENikolino%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1558869%22%20slang%3D%22en-US%22%3EBetreff%3A%20Auto%20Fill%20columns%20based%20on%20filled%20cells%20to%20the%20left%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20numbers%20in%20column%203%20are%20still%20filling%20to%20match%20the%20cells%20in%20column%20D.%20I%20want%20them%20to%20only%20go%20down%20as%20far%20as%20column%20B%2C%20but%20I%20still%20want%20to%20have%20the%20formula%20in%20column%20D%20down%20to%20the%20bottom%20of%20the%20page.%20I%20know%20that%20if%20I%20put%20the%20formula%20in%20column%20D%20only%20to%20the%20rows%20filled%20in%20columns%20A%20-%20C%20it%20works%2C%20but%20I%20want%20to%20be%20able%20to%20have%20the%20formula%20fill%20to%20the%20bottom%20of%20the%20column%20without%20the%20price%20auto-filling%20to%20that%20same%20row.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1558977%22%20slang%3D%22de-DE%22%3ESubject%3A%20Auto%20Fill%20columns%20based%20on%20filled%20cells%20to%20the%20left%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558977%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746068%22%20target%3D%22_blank%22%3E%40excel_user_1720170%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EAccording%20to%20the%20translation%20it%20should%20be%20like%20this%20...%20if%20not%20please%20report%20again%20and%20if%20possible%20show%20more%20specifically%20with%20the%20worksheet%20...%20since%20my%20english%20is%20not%20the%20best.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3Ethank%20you%20%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1559077%22%20slang%3D%22en-US%22%3EBetreff%3A%20Auto%20Fill%20columns%20based%20on%20filled%20cells%20to%20the%20left%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559077%22%20slang%3D%22en-US%22%3Ein%20cell%20D1%20write%3A%3CBR%20%2F%3E%3DIF(B1%26lt%3B%26gt%3B%22%22%2CB1*C1%2C%22%22)%3CBR%20%2F%3Ethen%20autofill%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1559518%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Fill%20columns%20based%20on%20filled%20cells%20to%20the%20left%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746068%22%20target%3D%22_blank%22%3E%40excel_user_1720170%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20former%20time%20it%20worked%20differently.%20But%20there%20were%20issues%20with%20blank%20cells%20in%20the%20middle%20of%20adjacent%20column.%20After%20the%20the%20improvement%2C%20or%20now%20if%20you%20don't%20consider%20that%20as%20improvement%2C%20fill%20handle%20works%20till%20last%20not%20blank%20cell%20in%20both%20adjacent%20columns%2C%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20234px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209486i28B5C6FECD6BB5DB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ETo%20my%20knowledge%20there%20is%20no%20way%20to%20change%20such%20behaviour.%20Workaround%20could%20be%20(as%20in%20your%20sample%20if%20you%20stay%20on%20the%20first%20cell%20of%20third%20column)%3C%2FP%3E%0A%3CP%3ELeft%3C%2FP%3E%0A%3CP%3ECtrl%2BDown%3C%2FP%3E%0A%3CP%3ERight%3C%2FP%3E%0A%3CP%3ECtrl%2BShift%2BUp%3C%2FP%3E%0A%3CP%3ECtrl%2BD%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Older versions of Excel would let me fill a column to the row level with the data left of that row. Now when I double click the autofill handle in the first cell, it fills all the way down to match the numbers in the column to the right. Is there a way to set up the columns to fill only to the level of those numbers listed to the left of the column? I'm doing a worksheet that lists different types of wood, the board feet and the cost per foot. I then want to calculate what that wood will cost. I don't want to autofill to the level of cells with the formula, but just to the level of cells with the type and board ft. When I double click the autofill handle in the first cell of the third column, I only want it to fill to the point where the first two columns end.  I've attached a file to help explain since I don't know if I'm making much sense.

5 Replies

@excel_user_1720170 

 

Quickly, I hope I got it right through the translation.
Please open the inserted worksheet.


If you liked my solution, please mark it as the correct answer ... if possible with a like (Thumbs up

so that everyone else can be informed. If you didn't like my answer, please give me a quick feedback.


Thank you
Nikolino
I know I don't know anything (Socrates)

@Nikolino  

The numbers in column 3 are still filling to match the cells in column D. I want them to only go down as far as column B, but I still want to have the formula in column D down to the bottom of the page. I know that if I put the formula in column D only to the rows filled in columns A - C it works, but I want to be able to have the formula fill to the bottom of the column without the price auto-filling to that same row.

@excel_user_1720170 

 

According to the translation it should be like this ... if not please report again and if possible show more specifically with the worksheet ... since my english is not the best.

 

thank you
Nikolino
I know I don't know anything (Socrates)
in cell D1 write:
=IF(B1<>"",B1*C1,"")
then autofill

@excel_user_1720170 

Yes, former time it worked differently. But there were issues with blank cells in the middle of adjacent column. After the the improvement, or now if you don't consider that as improvement, fill handle works till last not blank cell in both adjacent columns, like this

image.png

To my knowledge there is no way to change such behaviour. Workaround could be (as in your sample if you stay on the first cell of third column)

Left

Ctrl+Down

Right

Ctrl+Shift+Up

Ctrl+D