76

I use three columns. A, B and C. In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2.

How can I do so I don't have to type in the new formula in column C for every row?

I use it in both Google Docs SpreadSheet and OpenOffice SpreadSheet.

89

Using the Mouse

  1. Click the cell whose formula you want to repeat
  2. A dark square "handle" will appear in the lower right corner

    Location of Drag box

  3. Click and drag that handle, dragging down the column (or right across the row). You can also double click the handle to auto-fill.

    Dragging the box

  4. Stop at the last cell you wish to fill

Using the Keyboard

  1. Move the cursor to the cell whose formula you want to repeat
  2. Hold shift
  3. While holding, press down repeatedly to select the rest of the range of cells you want to fill
  4. When you reach the bottom, release shift then press CTRL + D (Use CTRL + R if you're filling to the right)(Using this method also preserves notes, unlike the mouse solution.)

In both cases what you're doing is called "filling." It is supported by every(?) spreadsheet program.

  • 5
    Just FYI, in excel, you can double click the bottom, right corner of the selected cell to copy the data down the column down as long as a neighboring cell has data. The bottom right corner should have a little black box on it, you click that. – skub Mar 22 '11 at 0:00
  • 1
    @BorisCallens, filling shortcuts are in Ctrl-/ popup help. – anatoly techtonik Feb 12 '14 at 17:54
  • 1
    I have a big range, and really need the fastest way to copy the formula on all rows. The double click on bottom right corner is not working in Google Docs – Kostanos Jun 30 '14 at 23:32
  • 3
    @Kostanos - See ceoliphant's answer below. It should do what you need. – Geoff Aug 11 '14 at 20:35
  • 3
    On Mac you have to use ⌘-D. It's is also bound to "Create Bookmark" in Chrome, but still works! Also, if you configure the first cell in the column, you can simply click the column title to select the entire thing, then press Ctrl-D or ⌘-D to fill down. – jchook Oct 29 '17 at 23:37
78

An even easier solution in Google Sheets would be to enter this formula in C1:

=ARRAYFORMULA(IF(A5:A,A5:A*(1.6*B5:B),""))

It automatically propagates to subsequent rows if a value is entered in column A, removing the need to copy it to each row. In fact, if you copied it to C2, it would be automatically overwritten by the continuation of the formula in C1.

The important part is the :A and :B, which specify you'd like to include these entire columns in your formula. This means you could apply the single cell formula =A5*(1.6*B5) to entire columns with:

=ARRAYFORMULA(A5:A*(1.6*B5:B)) 

Note that this yields bad results where A and B are missing values, so we wrap it in an IF() statement (see above) to show nothing when there are no values. You could also use IFERROR() to handle bad results.

  • This is great. I wonder how you would modify it if I want to copy it to every alternate row? – shparekh Sep 11 '18 at 1:22
  • The references to specific cells (A5, B5) is unexpected/unnecessary here; @skube's answer below seems more straightforward: superuser.com/a/986607/9096. – mjs Nov 4 at 12:54
13

here is a another way, go ahead and delete all the formulas that are in there right now, then type in the formula in C1 having it correspond to A1 and B1 and hit enter.
so now the correct formula is just in C1,
now click the C1 box, a bounding box will appear, the bottom right corner of this bounding box has a dark square,
double click this square and the formula will 'fill down'
you will notice C2 corresponds to A2 and B2 and so on.
if this is what you need and i am understanding correctly

  • 1
    i just noticed you werent using excel, this might not work then – fightermagethief Mar 21 '11 at 22:22
  • +1 Thanks, this was good but it only works for me in OpenOffice and not in Google Docs. – Jonas Mar 21 '11 at 22:29
  • 3
    Worked for me (somewhat later in history) in Google Docs, er, Drive. – RTS - read about Monica Cellio Nov 27 '12 at 21:03
  • Now works in google sheets. I just tried it after finding this question. – Sean Perry Jul 26 '18 at 22:08
9

The suggested answers work well for small sheets but I had thousands of rows and using the mouse or the keyboard to select them was simply too time consuming.

The ARRAYFORMULA method works but it's complicated, forces me to rewrite formula style and consider possible errors).

The solution is so simple it can be done in 2 seconds:

  1. Write your new formula in the first CELL.
  2. click on the cell, press CTRL+C (copy the cell)
  3. click on the column header (for example A) to select the whole column
  4. CTRL+V -> paste the cell formula into the whole column
  5. profit
  • 3
    Finally someone as lazy as me! For Google Sheets at least this should be the defacto answer. – Tom Nov 2 '17 at 11:49
  • Can this be done to only process if the other two cells are populated, I end up with a column full of 0s after the ones I did – TheHamstring Feb 7 '18 at 11:17
5

I found all of these solutions very frustrating and confusing also.

I will warn you though, this will replace whatever is currently in the cells, but as it is a formula this should not be a problem.

For me it was simple.

  1. Click the cell whose formula you want to copy once (select it)
  2. Copy the cells contents (Ctrl+C on Windows, cmd+C on macOS)
  3. Hold Shift+Ctrl+Down (selecting all of the cells in that row)
  4. Now Paste the formula as you have all of the cells selected.

This will put the formula, updated with each cells on references.

4

After you write your forumla, you can double click the bottom, right corner of the selected cell with the blue box, to copy the data down the column down as long as a neighboring cell has data.

This saves a lot of time when you have a 7,000 row sheet you are working with.

enter image description here

  • why it doesnt work now? – fdrv May 6 '16 at 1:15
  • This is the best answer. Double click on the box on the bottom right to autofill the rest of the column – helsont Jul 16 '17 at 2:01
  • This only works if the cells below do not have data inside; but this is by far the easiest solution. – Dan Walters May 30 at 10:25
3

P.S. I am working in OpenOffice, and now I see that it also works by simply copying the content of the cell and pasting it into the other ones. The formula is automatically adjusted to each row! (To avoid the automatic adjustment prefix the name of row number and column numbers with a $).

3

Very similar to ceoliphant's answer but a little more straightforward, simply add one formula to C1:

=ARRAYFORMULA(iferror(A:A*B:B*1.6))
2

You could select the complete column C by selecting the header and paste the formula =A1*(1.6*B1) . it will apply to every row.

No need to select and drag to copy to every cell.

  • 2
    how to paste it to all the cells? as I do it, the formula is copied to the first cell only – Moisei Oct 6 '13 at 14:53

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