Shortcut to Apply a Formula to an Entire Column in Excel

Question

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows!

Is there a shortcut, similar to CTRL+SPACE, that will apply a formula to the entire column, or to a selected part of the column?

score 678 · Accepted Answer · answered Mar 24 '11 at 20:41

678

Try double-clicking on the bottom right hand corner of the cell (ie on the box that you would otherwise drag).

answered Mar 24 '11 at 20:41

soo

7,100
1
18
10

19

Works perfectly in LibreOffice Calc as well, +1. – Jonah Feb 26 '13 at 19:13
6

@soo Is there a way we can programmatically do it, so that once new row entries are defined, the formula for sum automatically applies to the cell in the new row. – Ankit Dhingra Mar 04 '13 at 11:35
13

Nothing happened when I did this in Office for Mac; I'm sure there's a discrepancy between the two versions. – 2rs2ts Jun 06 '13 at 17:57
You glorious individual. Worked great on Office for Mac. MacOS 10.8.4 with Excel 2008 Version 12.1.1 – Matthew Blancarte Jul 01 '13 at 20:18
Did not work for me. Tried Phil's answer instead with success. Using Excel 2007. – Chry Cheng Nov 22 '13 at 08:18
also works for libreoffice. – tanaydin Nov 22 '13 at 13:03
2

also does not work on excel 2010 – Evil Washing Machine Feb 26 '14 at 14:02
If this doesn't work, you may try clearing the entire column first; obviously not your cell with formula though. – Joel Mellon Apr 30 '14 at 20:09
Wow, I'm amazed that I've never heard of this tip before... I wonder how long Excel has had this little capability? – Holistic Developer May 14 '14 at 03:02
4

This did not work in Excel 2013 with the formula already applied to one cell. Ctrl+D worked in RobinCTS post. – Brett Mathe Jun 19 '14 at 14:35
works even in WPS office 10.1 (linux version) – jena Apr 01 '17 at 08:42
How is double-clicking a shortcut? – Eric12345 Aug 11 '17 at 12:20
2

This will not work if the formula column is not right against the data column so make sure there is no blank column in between. – James Jan 23 '18 at 21:33
Works for me in Excel 2010. @EvilWashingMachine – suhao399 Mar 29 '18 at 08:16
nicely hidden, microsoft excel crashes when attempted this with a match function or vlookup on a dataset spanning million rows, totally as expected from office 365 in 2019 – computingfreak Apr 10 '19 at 06:23
In Mac version 16.25 it didn't work by default. But when I add filtering (Home -> Sort & Filter), so those arrows directed to down was added to every column I was able to double-click and it nicely copy my formula to all cells in column. – Łukasz Gawron May 15 '19 at 15:00

score 231 · Answer 2 · edited Jun 14 '17 at 16:13

231

If the formula already exists in a cell you can fill it down as follows:

Select the cell containing the formula and press CTRL+SHIFT+DOWN to select the rest of the column (CTRL+SHIFT+END to select up to the last row where there is data)
Fill down by pressing CTRL+D
Use CTRL+UP to return up

On Mac, use CMD instead of CTRL.

An alternative if the formula is in the first cell of a column:

Select the entire column by clicking the column header or selecting any cell in the column and pressing CTRL+SPACE
Fill down by pressing CTRL+D

edited Jun 14 '17 at 16:13

Asclepius

57,944
17
167
143

answered Jan 18 '13 at 03:02

robinCTS

5,746
14
30
37

2

Whether it slow or not, but no regret to try this one, but it will really slow just for making the formula on to the bottom. Total rows on Libre Office is 1,048,576 rows when I tried this method, it took me around 15 minutes just to fill a row number formula. :D – ksugiarto Oct 24 '13 at 05:50
ctrl + shift + down does not work on mac – JqueryToAddNumbers Jul 21 '15 at 09:34
14

Mac users: `cmd + shift + down`, then `ctrl + d` – clmarquart Feb 15 '16 at 12:02
3

For only (not empty) column use: CTRL+SHIFT+END – Conan Jul 12 '16 at 08:01
Works perfectly on Google Sheets too. – TheWanderer May 14 '18 at 22:16
FYI, if you know how many rows you're working with, it's more efficient to Ctrl+G select a cell in the last row, then do Ctrl+Shift+UP, so that you don't apply the formula to a bunch of empty cells. – fncomp Jul 26 '18 at 21:30
Ctrl+space and ctrl+down is the answer I was looking for, thanks @robinCTS – Nirmal Mangal Feb 21 '19 at 20:41
When applying the same formula in the whole row, `fill right (CTRL + R)` and not down. – paradocslover May 10 '19 at 04:24

score 95 · Answer 3 · answered Mar 01 '12 at 15:28

95

Select a range of cells (the entire column in this case), type in your formula, and hold down Ctrl while you press Enter. This places the formula in all selected cells.

answered Mar 01 '12 at 15:28

Phil

1,059
7
3

Wow. Yes you are right. I didn't know what you meant by "introducing a `Shift`" but you are absolutely correct. I was describing a `Ctrl+Shift+Enter` behavior, not a `Ctrl+Enter` behavior! – Michael Delgado Jul 27 '16 at 00:11

Shortcut to Apply a Formula to an Entire Column in Excel

3 Answers3

Linked