How to apply a formula to an entire column in Google Sheets

You can watch a video tutorial here.

One of the great things about Google Sheets is the ease with which complex formulas and calculations can be done. Using cell references it is easy to create formulas that can be copied to other rows or columns. The cell references change according to the direction in which the formula is copied. If the formula is copied down a column, the row number changes but the column remains the same. If it is copied across a row, the column changes and the row number remains the same.

You may have a worksheet in which a formula has to be copied to the entire column. This can be done using copy and paste or by dragging the fill handle, but if the sheet has 1000 rows, then it becomes a time-consuming process. There are a few ways in which a formula can be applied to an entire column in Google Sheets:

  1. Using the fill handle
  2. Using Ctrl+D or Ctrl+Enter

Option 1 – Use the fill handle

Step 1 – Type the formula

  • Select the first cell of the column that is to contain the formula
  • Type the formula using cell references

=5/100*Cost

Step 2 – Copy the formula

  • Select the cell with the formula
  • Move the pointer over the fill handle i.e. the box in the lower right corner of the cell
  • When the pointer changes to a cross, double-click
  • The formula will be copied to the rest of the column

Option 2 – Use Ctrl+D

Step 1 – Type the formula

  • Select the first cell of the column that is to contain the formula
  • Type the formula using cell references

=5/100*Cost

Step 2 – Copy the formula

  • Select the cell with the formula
  • Press Ctrl+Shift + Down arrow  to select the entire column
  • Press Ctrl+D or Ctrl+Enter
  • The formula will be copied to the rest of the column