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:

enter image description here

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?

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,"")`. — aderchox, Dec 04 '21 at 06:57

score 149 · Answer 1 · edited Oct 03 '22 at 11:56

149

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 (Cmd + Enter on Mac.)

(Note that this replaces text if the destination cells aren't empty)

edited Oct 03 '22 at 11:56

General Grievance

4,555
31
31
45

answered Sep 25 '18 at 17:01

Shameen

2,656
1
14
21

4

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. – Adam Barnes Jul 13 '19 at 00:02
This worked like a charm. You are my hero. – Chris Chang Feb 09 '23 at 03:45

score 125 · Answer 2 · edited Oct 03 '22 at 11:58

125

I think it's a more recent feature, but...

Double clicking the square on the bottom right of the highlighted cell copies the formula of the highlighted cell.

edited Oct 03 '22 at 11:58

General Grievance

4,555
31
31
45

answered May 07 '15 at 09:42

FredFury

2,286
1
22
27

25

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. – Thanasis Kapelonis May 03 '16 at 18:03
4

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 May 20 '16 at 02:31
3

@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 Feb 14 '17 at 14:41
2

I have too much data to do this answer, it fails and I have to reload the page. – Daniel Ryan May 04 '17 at 22:10
Yep this was it! I had thousands of rows! Thank you – Script Kitty May 17 '17 at 02:50
Works great for me! Thanks! – Andreas Rolén Sep 26 '17 at 12:55
1

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 Apr 05 '18 at 14:43
10

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. – Kilo Jun 29 '19 at 20:39
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! – eternal_student Feb 16 '21 at 18:07
I tried the double click trick for a `VLOOKUP` formula but didn't get it to work as it returned `#N/A`. However when I wrap the `VLOOKUP` in `IFNA(VLOOKUP(…), "Not found")` if does work. This does not work for empty cells though. – gabrielf Dec 16 '22 at 08:30
For me, this only worked if the cell was not selected. – daniella Jan 04 '23 at 20:48

score 67 · Answer 3 · edited Oct 03 '22 at 12:04

67

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.

This is a shortcut for wrapping the formula with ArrayFormula():

=ArrayFormula(text(A1:A,"00000"))

edited Oct 03 '22 at 12:04

General Grievance

4,555
31
31
45

answered Jul 25 '14 at 23:59

pnuts

58,317
11
87
139

4

@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 Aug 04 '15 at 23:04
12

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. – Shahab Yarmohammadi May 20 '16 at 02:33
5

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. – LOAS Oct 25 '16 at 09:10
1

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. – volker Feb 18 '18 at 18:42

score 32 · Answer 4 · edited May 02 '22 at 14:04

32

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.

edited May 02 '22 at 14:04

Rob Bednark

25,981
23
80
125

answered Jan 24 '21 at 09:50

Debu Shinobi

2,057
18
21

And if you use mac, replace `Shift` with `Command`. – hlin117 Mar 11 '22 at 16:24

score 18 · Answer 5 · answered Dec 26 '19 at 04:07

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.

Replace `Ctrl` with `Cmd` in macOs and it would still work, thank you — ericn, Sep 14 '21 at 18:13

score 12 · Answer 6 · edited May 13 '22 at 10:20

12

For Mac:

Click on the first cell having the formula and press Ctrl + Shift + down_arrow. This will select the last cell in the column used on the worksheet.

`Command + D`

(don't use ctrl). This will fill the formula in the remaining cells.

edited May 13 '22 at 10:20

seralouk

30,938
9
118
133

answered Feb 09 '21 at 22:12

spidyj

321
3
6

score 9 · Answer 7 · answered Apr 05 '18 at 14:19

Let's say you want to substitute something in an array of string and you don't want to perform the copy-paste on your entire sheet.

Let's take this as an example:

String array in column "A": {apple, banana, orange, ..., avocado}
You want to substitute the char of "a" to "x" to have: {xpple, bxnxnx, orxnge, ..., xvocado}

To apply this formula on the entire column (array) in a clean an elegant way, you can do:

=ARRAYFORMULA(SUBSTITUE(A:A, "a", "x"))

It works for 2D-arrays as well, let's say:

=ARRAYFORMULA(SUBSTITUE(A2:D83, "a", "x"))

George · Answer 8 · 2019-03-19T17:04:03.050

9

Found another solution:

Apply the formula to the first 3 or 4 cells of the column
Ctrl + C the formula in one of the last rows (if you copy the first line it won't work)
Click on the column header to select the whole column
Press Ctrl + V to paste it in all cells bellow

edited Mar 19 '19 at 17:04

answered May 04 '18 at 14:07

George

6,886
3
44
56

3

This seems to work more reliably than double-clicking for me. Thanks! – Ivan -Oats- Storck May 31 '18 at 18:50

score 8 · Answer 9 · edited Dec 11 '20 at 16:51

8

Just so I don't lose my answer that works:

Select the cell to copy
Select the final cell in the column
Press CTRL+D

edited Dec 11 '20 at 16:51

Neuron

5,141
5
38
59

answered Apr 09 '20 at 01:49

Valentin Kuzub

11,703
7
56
93

2

Nice! Note: it should be shift-click for step 2. – Caleb Stanford Nov 19 '20 at 02:52

score 5 · Answer 10 · answered Feb 21 '23 at 18:41

Reading the answers here did not quite work for me, but a combination did. Here are my steps (Windows, Chrome):

Select the cell with the formula you want to apply to the column (for the whole column, ensure this is the top cell)
Ctrl + Shift + Down (selects all below cells)
Ctrl + D (apply formula)

Done!

Based on:

score 4 · Answer 11 · answered Jul 18 '18 at 22:20

4

You can use Ctrl+Shift+Down+D to add the formula to every cell in the column as well.

Simply click/highlight the cell with the equation/formula you want to copy and then hold down Ctrl+Shift+Down+D and your formula will be added to each cell.

answered Jul 18 '18 at 22:20

dig potts

73
1

score 2 · Answer 12 · answered Feb 22 '21 at 21:37

The exact formula is:

=ArrayFormula(text(A1:A,"00000"))

ArrayFormula works on multiple rows (in the above example, every row), and results are placed in the cell with the formula and the cells below it in the same column. It looks as if the same formula was copied into all those rows.

If any of the cells in that column are not empty, they won't get overwritten. Instead, you will get an error message.

To save yourself typing, you can use the trick from the answer above by pnuts:

Type: =text(A1:A,"00000") and then hit the following key combination:

On windows: Ctrl+Shift+Enter
On a MAC: Command+Shift+Enter

This will convert the formula to ArrayFormula.

After hitting the key combination, you need to hit Enter, to actually apply the converted formula.

If your sheet contains header row(s), and you want to apply formula from (for example) row 5 on, you would use =text(A5:A,"00000") instead.

This answer includes information from pnuts's answer and LOAS's comment.

score 1 · Answer 13 · answered Sep 26 '22 at 15:47

Lambda Solution

Using the new LAMBDA and MAP functions, this is now doable without an ArrayFormula or having to drag anything.

=MAP(A2:A6, LAMBDA(value, TEXT(value, "00000")))

LAMBDA defines a function. value is the parameter, which we can use in the formula expression.
MAP applies the LAMBDA to each value in the given range. This also works on 2D ranges.

score 0 · Answer 14 · answered Jul 09 '16 at 03:17

0

To be clear when you us the drag indicator it will only copy the cell values down the column whilst there is a value in the adjacent cell in a given row. As soon as the drag operation sees an adjacent cell that is blank it will stop copying the formula down.

.e.g

1,a,b
2,a
3,
4,a

If the above is a spreadsheet then using the double click drag indicator on the 'b' cell will fill row 2 but not row three or four.

answered Jul 09 '16 at 03:17

Brett Sutton

59
3

1

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. – dresh Feb 14 '17 at 16:56
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 Apr 05 '18 at 14:43

user15476563 · Answer 15 · 2021-03-25T08:51:01.333

Well, Another easiest and simplest way is as in my file rows were above 16000, which is pretty huge number. So steps which helped me are:

1. Select the cell in which formula is written.
2. Then go to NameBox(it is the box which tells about active cell). Here in my case it was the cell where was formula was written(i.e P2).
3. Then in that cell type your active cell number:your last row.For example last row of my column was 16745 and formula was written in P2. So write P2:P16745,
4. Press Enter in Name Box and bingo your whole area of column till last row is selected.
5. Now press Ctrl+D(Windows)

score 0 · Answer 16 · answered Oct 03 '22 at 18:30

0

try:

=INDEX(TEXT(A2:A; "00000"))

advantages: short, works, smells nice

to map out empty cells you can do:

=INDEX(IF(A2:A="";;TEXT(A2:A; "00000")))

answered Oct 03 '22 at 18:30

player0

124,011
12
67
124

note that INDEX will output the formatted date as datevalue – player0 Oct 03 '22 at 18:39

score -2 · Answer 17 · edited Mar 29 '18 at 02:13

-2

You may fill the column by double-clicking on the bottom right hand corner of the cell which you want to copy from (the point on the box that you would otherwise drag) and it will be applied to whole column.

NB: This doesn't work if you have the filter applied, nor if there is already something already in the cells below.

edited Mar 29 '18 at 02:13

Community

1
1

answered Apr 13 '17 at 16:56

ajay

23
3

This is correct, however, you must make sure that no data is in the cells beneath the cell containing your formula. – BinaryJoe01 Dec 14 '17 at 23:49
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 Apr 05 '18 at 14:42

Apply formula to the entire column

17 Answers17

`Command + D`

Lambda Solution

Linked

Related