functions on whole columns

E

ellie

I'm new to Excel, please forgive me if these are silly questions...

1/

I have three columns - the cells in A and B contain numbers
and I want the cells in C to be those in B minus those in A
i.e.

C1 = B1 - A1
C2 = B2 - A2
and so on.

To achieve this, I don't want to have to go down the whole column
typing in box C1 " =B1-A1 " and typing in C2 " =B2-A2 "
and so on.

Is there a way I can apply one function to the whole of column C
to save time typing?

2/

Different problem...

ROW () gives the row number. So if I type in box C6, ROW() it gives the
number 6.
But if I want C6 to contain the contents of B6, why doesn't =B(ROW()) work
i.e. the ROW() is 6 so it's equal to "=B6". What am I doing wrong?

Many thanks for any advice.
 
B

Bernard Liengme

Two ways:
1) Click on column header to select the entire column; type =B1-A1 and then
use CTRL+ENTER to complete the formulas
2) In C1 enter =A1-B1 and double click C1's fill-handle (the little solid
square in the cell's lower right corner - when cell is selected). This works
only when there are entries in the neighbouring column.
best wishes
 
D

Dave Peterson

You can use the autofill button on the bottom right corner to drag down the
formula as far as you need.

Debra Dalgleish has some tips at:
http://contextures.com/xlDataEntry01.html#Mouse

or....

You can select the range you want first:
Say you select C1:C99
Then type the formula for C1:
=b1-a1
But instead of hitting enter, hit ctrl-enter. Excel will adjust the formula for
each cell in the selection.

============
You can use =indirect() to point at other cells:

in A6, this will return the value in B6:
=indirect("b"&row())
 
P

pinmaster

You don't have to type your formula over and over again, just type it i
C1 and then drag the cell down as far as needed.

HTH
J
 
Top