Apply formula to the entire column

Question

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?

Solution 1

It looks like some of the other answers have become outdated, but for me this worked:

1. Click on the cell with the text/formula to copy
2. Shift+Click on the last cell to copy to
3. 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.

1. Select the column cell you would like to copy downwards
2. Press Ctrl+Shift+⇓ to select the cells below
3. 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.

View more solutions

Comments

[aderchox]

Also 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]

When I try Ctrl+Shift+Enter, it just surrounds my formula with ArrayFormula(…).

[Jay]

@pnuts what so you mean it should ? this doesn't work for me either . keep getting ArrayFormula(

[Assimilater]

@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]

@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]

This 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]

Thanks 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]

Thanks 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]

Thanks Shahab for clarifying that. I was indeed missing on that part.

[LOAS]

This 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]

How would this work for the sum of 2 cells. Like stackoverflow.com/questions/40497946/…

[dresh]

@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]

If 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]

I have too much data to do this answer, it fails and I have to reload the page.

[Script Kitty]

Yep this was it! I had thousands of rows! Thank you

[Andreas Rolén]

Works great for me! Thanks!

[Robert M.]

Worked for me too. If you're confused, check out this: sheetgo.com/arrayformula-google-sheets

[BinaryJoe01]

This is correct, however, you must make sure that no data is in the cells beneath the cell containing your formula.

[volker]

If it doesn't work look at your formula check the cell format. We usually would write =text(A1,"00000") (just A1) 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]

While 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]

While 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]

While 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]

This seems to work more reliably than double-clicking for me. Thanks!

[StapleGun]

Works with CMD + Enter on Mac.

[Happy Bird]

This the best answer in 2019.

[phlare]

This answer works for a much wider range of formulas, and should be the accepted answer

[Kilo]

This 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]

Only 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, then Up, Right, [Down] (last one won't be needed if you're on row 1) to get to the next column.

[bernk]

This 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]

Works like a charm in 2020. Thanks!

[Ole S]

This is the most straight hack. Thanks!

[Lod]

Works great. At last after years looking for a simple way to achieve that. What a time saver! Thanks.

[superiggy]

For God's sake. I LOVE YOU. This works perfectly. (In 2020)

[Sakshi]

best answer. Works like a charm

[6005]

Nice! Note: it should be shift-click for step 2.

[eternal_student]

While 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]

Only working answer in 2021 (cmd + Enter for Mac). Thanks for this.

[ericn]

Replace Ctrl with Cmd in macOs and it would still work, thank you

[pilcrowpipe]

Someone buy that man a beer! Thanks, best method working 2022.

[hlin117]

And if you use mac, replace Shift with Command.

[Blake Frederick]

Thanks, didn't know the Ctrl + D bit

[seralouk]

At least a working answer for MacBooks.