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.
A dark square "handle" will appear in the lower right corner
Click and drag that handle, dragging down the column (or right across the row). You can also double click the handle to auto-fill.
Stop at the last cell you wish to fill
shift down repeatedly to select the rest of the range of cells you want to fillshift 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.
Ctrl-/ popup help.
Commented
Feb 12, 2014 at 17:54
⌘-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.
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.
A5, B5) is unexpected/unnecessary here; @skube's answer below seems more straightforward: superuser.com/a/986607/9096.
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:
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
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.
This will put the formula, updated with each cells on references.
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))
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.
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 $).
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.
