How to create a formula for every row in a column in Google SpreadSheet?

Question

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.

Solution 1

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

   

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.

   

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.

Solution 2

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.

Solution 3

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

Solution 4

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

Solution 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.

View more solutions

Comments

[Jonas]

Is there any way I can type this? because I use a laptop and it is hard to select the hole column....

[Geoff]

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]

I added a keyboard-only option to the answer.

[fightermagethief]

i just noticed you werent using excel, this might not work then

[Jonas]

+1 Thanks, this was good but it only works for me in OpenOffice and not in Google Docs.

[skub]

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.

[Robert Tupelo-Schneck]

Worked for me (somewhat later in history) in Google Docs, er, Drive.

[Boris Callens]

@skub: I was looking for that function. Don't think it exists in docs?

[Moisei]

how to paste it to all the cells? as I do it, the formula is copied to the first cell only

[anatoly techtonik]

@BorisCallens, filling shortcuts are in Ctrl-/ popup help.

[Kostanos]

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

[Geoff]

@Kostanos - You can hold SHIFT and press PGDN to select lots of rows, then use CTRL+D to fill down.

[Kostanos]

Still to slow for huge number of rows. Thank you anyway

[Geoff]

@Kostanos - See ceoliphant's answer below. It should do what you need.

[user2428118]

You can also use <kbd>Page Down</kbd> instead of <kbd>Shift</kbd>. This is much faster if you need to fill a large number of rows.

[Dmytriy Voloshyn]

I can confirm that this works in Google Spreadsheets: 1. Select cell with formula 2. Press Shift + Command + Down to select all cels below 3. Press Command + D, to fill all the range. Works like a charm for ~40000 rows

[fdrv]

why it doesnt work now?

[Yaroslav]

Also you can select cell or cells with formula you want to populate, scroll down to latest cell, while Shift click on the last cell. You will select all array from your first to last cell. And then click Ctrl+D (Cmd+D on Mac) to fill out cells with formula.

[helsont]

This is the best answer. Double click on the box on the bottom right to autofill the rest of the column

[uc8293]

PURELY MAGIC!!!

[jchook]

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.

[Tom]

Finally someone as lazy as me! For Google Sheets at least this should be the defacto answer.

[Jamie Hutber]

This will also copy the VALUE!!!

[TheHamstring]

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

[Geoff]

@JamieHutber - who are you replying to? What will copy the value?

[Sean Perry]

Now works in google sheets. I just tried it after finding this question.

[shparekh]

This is great. I wonder how you would modify it if I want to copy it to every alternate row?

[Dan Walters]

This only works if the cells below do not have data inside; but this is by far the easiest solution.

[Dan Walters]

In Google sheets you can double click on the drag handler to copy the formula down every row - as long as these rows do not already have data.

[Prosinac Decembar]

The references to specific cells (A5, B5) is unexpected/unnecessary here; @skube's answer below seems more straightforward: superuser.com/a/986607/9096.

[John]

@TheHamstring You can extend your formula a little bit, add an IF case that only processes the field if it has content. In that case you can have the formula in all rows but it only outputs a value in those where you want it.