Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

5 Answers 5

up vote 29 down vote accepted

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)

    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)

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

share|improve this answer
    
Is there any way I can type this? because I use a laptop and it is hard to select the hole column.... –  Jonas Mar 21 '11 at 21:14
    
You can copy and paste over and over, but be sure to copy the cell (not the formula), to ensure the row numbers get updated. –  Geoff Mar 21 '11 at 21:38
3  
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. –  techtonik Feb 12 at 17:54
1  
@Kostanos - See ceoliphant's answer below. It should do what you need. –  Geoff Aug 11 at 20:35

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

share|improve this answer
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
2  
Worked for me (somewhat later in history) in Google Docs, er, Drive. –  Robert Tupelo-Schneck Nov 27 '12 at 21:03

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 $).

share|improve this answer

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.

share|improve this answer
1  
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

An even easier solution in Google Sheets would be the formula, entered in C1,

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

It will automatically replicate ("Continue") to succeeding rows if a value is entered in column A. No 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 in the formula. That indicates which entire columns you want included in the formula.

So for a single cell formula of =A5*(1.6*B5), you would apply this to entire columns with

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

This will give bad results where you don't have any values in rows A and B, so thus we add the IF() statement (as above) to show nothing when there are no values.

share|improve this answer

protected by Daniel Beck Aug 11 at 18:54

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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