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?

Asclepius

45.9k 14 gold badges 131 silver badges 117 bronze badges

asked Mar 24 '11 at 20:37

0

Comments disabled on deleted / locked posts / reviews  |

3 Answers 3

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

16

  • Works perfectly in LibreOffice Calc as well, +1.

    Feb 26 '13 at 19:13

  • @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.

    Mar 4 '13 at 11:35

  • Nothing happened when I did this in Office for Mac; I'm sure there's a discrepancy between the two versions.

    Jun 6 '13 at 17:57

  • also does not work on excel 2010

    Feb 26 '14 at 14:02

  • This did not work in Excel 2013 with the formula already applied to one cell. Ctrl+D worked in RobinCTS post.

    Jun 19 '14 at 14:35

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

Asclepius

45.9k 14 gold badges 131 silver badges 117 bronze badges

answered Jan 18 '13 at 3:02

8

  • 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

    Oct 24 '13 at 5:50

  • ctrl + shift + down does not work on mac

    Jul 21 '15 at 9:34

  • Mac users: cmd + shift + down, then ctrl + d

    Feb 15 '16 at 12:02

  • For only (not empty) column use: CTRL+SHIFT+END

    Jul 12 '16 at 8:01

  • Works perfectly on Google Sheets too.

    May 14 '18 at 22:16

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 1 '12 at 15:28

1

  • 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!

    Jul 27 '16 at 0:11

Not the answer you're looking for? Browse other questions tagged excel excel-formula or ask your own question.