Perform operations on a column

Using a fixed value

Using the referring feature of Excel you can easily create a new column by say adding, multiplying or performing any other operation on the old column.

For example, you can multiply age of cows by 12 to obtain approximate age in months or by 365 to obtain age in days.

  • Type Age (months) in cell H1 to create a header for the new column.
  • Type =E2*30 in cell H2 to calculate age of cow number 1 in months.
  • Having done this you don’t need to type this formula in each cell of column E. Simply select cell H2, and hover your mouse over the black square in the bottom right corner of the cell until the mouse turns from a hollow cross to a solid cross. Drag the mouse to fill cells up to H7- the formula will be copied in all the cells.
  • Alternatively, you could simply double click the black square (when the solid cross is displayed) to obtain the same results.

You could check the copied formula by clicking any of the cells from H2 to H7 - the formula will be displayed in the formula bar above. Alternatively, you could double click any of these cells to display the formula in the cell itself.

Using values from another column

Similar to above, you can multiply or perform any other operation on two or more columns. For example, you can divide column D by column C (though it wouldn’t make any sense!) by typing =D2/C2 in cell I2 and then copying the formula to all cells as done before (Try!).

Absolute and relative references

In the above example, C2 was multiplied by D2, C3 by D3, and so on. Sometimes, however, we wish to keep one of the cells the same but change the other. For example, we might wish to divide FIRSTBRD by some figure (say 10) stored in a cell (say K1) and store the resulting values in column J.

  • Type the formula =C2/K1 in cell J2.
  • Double click in cell J2, bring your cursor between K and 1 in the formula and press the function button F4 on your keyboard. Two $ symbols will be inserted in your formula which will now appear as =C2/$K$1. Reference for K1 now is an absolute reference compared to the relative reference before.
  • Simply drag the cell J2 down (as you have been doing before) to copy the formula to other cells. Note that the formula in cell J3 will be =C3/$K$1 and in cell J4 will be =C4/$K$1 and so on.
  • Note that two dollar signs mean that both column and row numbers are fixed. There will be situations where we only want either column or row number to be fixed. To do this, press F4 again after double clicking in the cell. This will change the column reference to relative but the row reference to absolute (K$1). Pressing F4 again changes the reference to absolute for the column and relative for a row ($K1). Further pressing F4 will change the reference back to absolute for both the row and the column ($K$1).

Exercise 4

  • Create a column by subtracting the column B from the column C.
  • Create a new column by dividing all cells in the column D by 30 to calculate calving to conception interval in months.
  • Now type 10 in a cell in your worksheet (say B15) and divide all cells in the column D by B15. Note that you will need to make the reference absolute.