Apply formula to the entire column
Solution 1
It looks like some of the other answers have become outdated, but for me this worked:
- Click on the cell with the text/formula to copy
- Shift+Click on the last cell to copy to
- Ctrl + Enter
(Note that this replaces text if the destination cells aren't empty)
Solution 2
I think it's a more recent feature, but it works for me:
Double clicking the square on the bottom right of the highlighted cell copies the formula of the highlighted cell.
Hope it helps.
Solution 3
I think you are in luck. Please try entering in B1:
=text(A1:A,"00000")
(very similar!) but before hitting Enter hit Ctrl+Shift+Enter.
Solution 4
This worked for me:
- Input the
formula
in the first cell. - Press Enter.
- Click on the first cell and press Ctrl + Shift + down_arrow. This will select the last cell in the column used on the worksheet.
- Ctrl + D. This will fill copy the formula in the remaining cells.
Solution 5
This is for those who want to overwrite the column cells quickly (without cutting and copying). This is the same as double-clicking the cell box but unlike double-clicking, it still works after the first try.
- Select the column cell you would like to copy downwards
- Press Ctrl+Shift+⇓ to select the cells below
- Press Ctrl+Enter to copy the contents of the first cell into the cells below
BONUS:
The shortcut for going to the bottom-most content (to double-check the copy) is Ctrl+⇓. To go back up you can use Ctrl+⇑ but if your top rows are frozen you'll also have to press Enter a few times.
skydv
Updated on July 08, 2022Comments
-
skydv almost 2 years
I'm changing all the zip codes from Column A into Column B with the formula:
=TEXT(A1,"00000")
Like this:
I want every cell in Column B to be transformed using the formula above. But I have over 40,000 rows, so it is not feasible to drag the formula down to apply it to the entire Column B because it takes so long.
Are there any alternatives to dragging?
-
aderchox over 2 yearsAlso might be useful to some: To ignore the cells that do not have the formula's required data and leave them blank use an if statement like this:
=IF(somecell<>"",yourformula,"")
.
-
-
binki over 9 yearsWhen I try Ctrl+Shift+Enter, it just surrounds my formula with
ArrayFormula(…)
. -
Jay almost 9 years@pnuts what so you mean it should ? this doesn't work for me either . keep getting ArrayFormula(
-
Assimilater over 8 years@pnuts maybe this has changed in the last year, but ArrayFormula does not extend the formula down Column B as the image from the OP. For me, It simply fills the value of the selected cell.
-
Assimilater over 8 years@pnuts maybe this has changed in the last year, but ArrayFormula does not extend the formula down Column B as the image from the OP. For me, It simply fills the value of the selected cell.
-
Thanasis Kapelonis almost 8 yearsThis is the correct answer to the question. Double clicking on "drag indicator" (bottom right corner) will copy the formula to all cells. Make sure you CLEAR your column data first and only apply your formula to one. The above answer with "ArrayFormula" seems to be able to apply a many cells to one formula, which is not what OP wanted.
-
Shahab Yarmohammadi almost 8 yearsThanks for sharing. It works fine for me but I prefer using the ArrayFormula function, simply because unlike your method which basically would copy the exact same formula down to every cell, ArrayFormula will be more dynamic and can be programmed more easily.
-
Shahab Yarmohammadi almost 8 yearsThanks for sharing. Worked really well for me. I think that one thing that folks may be missing is the B1:B part which I struggled with myself for a few second. You have to replace your single column, say B1 with an array of columns B1:B in your formula, in order for it to function. By far, this is the best method and the most dynamic.
-
synaptikon over 7 yearsThanks Shahab for clarifying that. I was indeed missing on that part.
-
LOAS over 7 yearsThis worked for me as well. Thanks for the clarifications in the comments. An additional thing that tripped me up was that the cells below must all be empty. I guess I thought they would just be overwritten.
-
Admin over 7 yearsHow would this work for the sum of 2 cells. Like stackoverflow.com/questions/40497946/…
-
dresh about 7 years@ThanasisKapelonis, you do not need to clear data, You just double click on the drag indicator and it applies the formula to all subsequent rows where data in each placeholder is available in corresponding columns for that row. It will stop where the data is missing. as shown by Brett Sutton
-
dresh about 7 yearsIf you are trying to apply the formula to rows in a filtered set in Excel by using the "Double click the drag indicator", it will stop every time the serial number of the row jumps, i.e row no. 1, 2, 3 if they appear in order will get applied with the formula, however if row no. 5 appears after row no. 3 (Due to filtering) then row no. 5 will not get the formula appled. In that case you have to copy from the cell in row no. 3 and paste it in row no. 5 and then use the same double click. Yeah, excel Sucks.
-
Daniel Ryan almost 7 yearsI have too much data to do this answer, it fails and I have to reload the page.
-
Script Kitty almost 7 yearsYep this was it! I had thousands of rows! Thank you
-
Andreas Rolén over 6 yearsWorks great for me! Thanks!
-
Robert M. over 6 yearsWorked for me too. If you're confused, check out this: sheetgo.com/arrayformula-google-sheets
-
BinaryJoe01 over 6 yearsThis is correct, however, you must make sure that no data is in the cells beneath the cell containing your formula.
-
volker about 6 yearsIf it doesn't work look at your formula check the cell format. We usually would write
=text(A1,"00000")
(justA1
) but for the array formula you need to write it as=text(A1:A,"00000")
(A1:A
). That makes the difference between working and not working. -
Toby Speight about 6 yearsWhile this might be a valuable hint to solve the problem, a good answer also demonstrates the solution. Please edit to provide example code to show what you mean. Alternatively, consider writing this as a comment instead.
-
Toby Speight about 6 yearsWhile this might be a valuable hint to solve the problem, a good answer also demonstrates the solution. Please edit to provide example code to show what you mean. Alternatively, consider writing this as a comment instead.
-
Toby Speight about 6 yearsWhile this might be a valuable hint to solve the problem, a good answer also demonstrates the solution. Please edit to provide example code to show what you mean. Alternatively, consider writing this as a comment instead.
-
Ivan almost 6 yearsThis seems to work more reliably than double-clicking for me. Thanks!
-
StapleGun over 5 yearsWorks with CMD + Enter on Mac.
-
Happy Bird over 5 yearsThis the best answer in 2019.
-
phlare almost 5 yearsThis answer works for a much wider range of formulas, and should be the accepted answer
-
Kilo almost 5 yearsThis doubleclick only seems to work when there is data in the column immediately beside the one you're filling... if there's e.g. an empty "spacer" column in between, it just fails, silently.
-
Adam Barnes almost 5 yearsOnly works on one column at a time, but works well. If you wanna get this done quickly for a lot of stuff,
Ctrl+Shift+Down
will select all the way to the bottom of the current column,Ctrl+Enter
like you said, thenUp, Right, [Down]
(last one won't be needed if you're on row 1) to get to the next column. -
bernk over 4 yearsThis should be marked as the correct answer, though it's not super clear. ArrayFormula is exactly what you want. It will apply the formula to every cell in that column.
-
nxmohamad about 4 yearsWorks like a charm in 2020. Thanks!
-
Ole S almost 4 yearsThis is the most straight hack. Thanks!
-
Lod almost 4 yearsWorks great. At last after years looking for a simple way to achieve that. What a time saver! Thanks.
-
superiggy almost 4 yearsFor God's sake. I LOVE YOU. This works perfectly. (In 2020)
-
Sakshi over 3 yearsbest answer. Works like a charm
-
6005 over 3 yearsNice! Note: it should be shift-click for step 2.
-
eternal_student about 3 yearsWhile technically correct, this can be tedious if you have a large number of rows. Also, you have to repeat the process every time you change the formula. Check the answer by @pnuts (currently the third answer, but I hope it will get more upvotes). I just tried that and it works like magic!
-
lextoumbourou almost 3 yearsOnly working answer in 2021 (cmd + Enter for Mac). Thanks for this.
-
ericn over 2 yearsReplace
Ctrl
withCmd
in macOs and it would still work, thank you -
pilcrowpipe about 2 yearsSomeone buy that man a beer! Thanks, best method working 2022.
-
hlin117 about 2 yearsAnd if you use mac, replace
Shift
withCommand
. -
Blake Frederick almost 2 yearsThanks, didn't know the Ctrl + D bit
-
seralouk almost 2 yearsAt least a working answer for MacBooks.